## You are learning SUM in MS Excel

SUM function with text (extracting numbers)?

The SUM function in Excel isn't designed to directly work with text to extract numbers. However, you can achieve the desired outcome by combining SUM with other functions depending on how the text and numbers are formatted. Here are two common scenarios:

Scenario 1: Text with Numbers Separated by Spaces

If your text has numbers separated by spaces, you can use the SUMIF or SUMIFS function along with the TEXTAFTER function to extract the numbers and then sum them.

Formula:

```excel

=SUM(TEXTAFTER(A2:A10," ")) // Assuming your text is in A2:A10

```

Explanation:

1. `TEXTAFTER(A2:A10," ")`: This part extracts everything after the first space in each cell of range A2:A10. Since the numbers follow the space, this isolates them.

2. `SUM()`: This function sums the values extracted by TEXTAFTER, assuming they are now numeric.

Scenario 2: Text Containing Numbers (Mixed)

If your text is mixed with numbers without any delimiters, extracting the numeric part requires a different approach. You can use a combination of functions like FIND, SEARCH, LEFT, and RIGHT to identify the starting and ending positions of the number within the text and then use SUM with the extracted numeric substring.

Formula (Example):

```excel

=SUM(LEFT(A2:A10,FIND(" ",A2:A10)-1)) // Assuming numbers end with a space

```

Explanation:

1. `FIND(" ",A2:A10)-1`: This part finds the position of the first space in each cell of range A2:A10 and subtracts 1 to get the length of the numeric substring (since numbers end with a space).

2. `LEFT(A2:A10,...)`: This extracts the leftmost characters from each cell in A2:A10, with the number of characters specified by the previous step.

3. `SUM()`: This function sums the extracted numeric substrings.

Note: These are just examples. You might need to adjust the formulas depending on the specific format of your text data. Consider using additional functions like IF or ISNUMBER to handle potential errors or edge cases.