Extracting Numbers from text

**hoventim** · 12-10-2012, 02:18 AM

I have seen another post which is over my head and uses macros to solve a similar problem.

Here is my problem I need to to solve..

A1 contains text "12c 1b 3w" or "12c1b3w" or "1c12b3w" or "12 1" or "6c" or "12b" or "7" or can be blank. 1 to 3 sets of numbers, 1 or 2 digits each.

I would like cell B1 to extract(?) any set (or single) numbers and add them together!

I am using the below formula and It works fine to get the first set of numbers. But I can't figure out how to get the second or third set of numbers (and better yet add the two together)

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$1000))))

Any help without macros? Thank you much in advance.

**:) Sixthsense :)** · 12-10-2012, 03:28 AM

Please attach a file with sample data and the expected output for better understanding.

**hoventim** · 12-10-2012, 03:50 AM

Thank you Sixth for your reply.

PLEASE DON"T USE MY FIRST EXAMPLE !!!

USE THIS NEW SECOND EXAMPLE !!!

Thank you

**vlady** · 12-10-2012, 04:13 AM

Example 1 - Extract Numbers.xlsx

maybe

**hoventim** · 12-10-2012, 04:47 AM

vlady that was awesome and worked fine for my example!

But I see now I need to change my example to include two digit numbers also..
See rows 8 and 9 in my new example.
Think it can still be done?

Below info not important to me, just curious about rows

BTW - when i attempt to manipulate your formula in Row 4, the "{" dissapears from the beginning of your formula and then it doesnt add up correctly?

And I don't seem to be able to reinsert it properly as in row 5... Why I wonder?

Thank you vlady

**:) Sixthsense :)** · 12-10-2012, 05:12 AM

Or

Copy and paste the below formula In D2 cell - Array Formula Requires Ctrl+Shift+Enter instead of Normal Enter

For 2003 version

Formula:

=SUM(IF(ISERR(--MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)),0,--MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)))

Drag it down...

For 2007 or higher version - Array Formula Requires Ctrl+Shift+Enter instead of Normal Enter

Formula:

=SUM(IFERROR(--MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1),0))

**hoventim** · 12-10-2012, 07:21 AM

I guess my example didn't include any double digit numbers like 12 or 17.
There will be no triple digit numbers though. Worked well with my sigle digit example.

I have included a third example using your formula here with double digits.

Also I would like to nest this in an if statement. I did it in rows 13 and 14, it seems to work the same way.

=IF(ISNUMBER(SEARCH(Employee,N7)),"YOUR_ARRAY_FORMULA","")

Thanks, Tim

**hoventim** · 12-10-2012, 04:56 AM

And I now see I'd like to put an "if" statement in front of your formual and the "{" dissapears and the formula doesnt add all the numbers? ARG... lol

**Ghozi Alkatiri** · 12-10-2012, 05:35 AM

try it (array formula)
=SUM(IFERROR(MID(A3;ROW($1:$100);1)/1;0))

copy down

**hoventim** · 12-10-2012, 06:10 AM

Ghozi thanks.

Only issue is some of my numbers are two digits long as in "12".
And it doesn't work on those.

Also I'd like to nest this in an if statement, can that be done with an array?
Can you look at my new example? (Example -2)

Thank you very much

**Marcol** · 12-10-2012, 10:47 AM

The most versatile way is to use VBa.

Try this UDF

Function SumCell(rng As Range)
    Dim strtmp As String
    Dim n As Long
    
    For n = 1 To Len(rng)
        If Asc(Mid(rng, n, 1)) >= 48 And Asc(Mid(rng, n, 1)) <= 57 Then
            strtmp = strtmp & Mid(rng, n, 1)
        Else
            strtmp = strtmp & " "
        End If
    Next
    strtmp = WorksheetFunction.Trim(strtmp)
    SumCell = Evaluate(Replace(strtmp, " ", "+"))
    
End Function

Use in worksheet

Formula:

=SumCell(A3)

**hoventim** · 12-10-2012, 06:06 PM

Marcol this works perfectly!

I will need to school myself on udf instalation and usage. Doesn't look to tuff for this newbie.

Thank you very much.

**mstubbs** · 12-10-2012, 06:32 PM

What about if your numbers are formatted in tenths and have ( ) around them? Such as:

ASDF(2.5); ASDF (3.6); ASDF (5.8)

Any way to get it to get you the total of the numbers in the same cell in the same format and only the numbers in between the ( )?

**Marcol** · 12-10-2012, 09:58 PM

Originally Posted by mstubbs

What about if your numbers are formatted in tenths and have ( ) around them? Such as:

ASDF(2.5); ASDF (3.6); ASDF (5.8)

Any way to get it to get you the total of the numbers in the same cell in the same format and only the numbers in between the ( )?

Assuming there are no "."s (dots) in the string, other than the decimal points.

Change the function to this ...

Function SumCell(rng As Range)
    Dim strtmp As String
    Dim n As Long
    
    For n = 1 To Len(rng)
        If Asc(Mid(rng, n, 1)) >= 48 And Asc(Mid(rng, n, 1)) <= 57 Or Asc(Mid(rng, n, 1)) = 46 Then
            strtmp = strtmp & Mid(rng, n, 1)
        Else
            strtmp = strtmp & " "
        End If
    Next
    strtmp = WorksheetFunction.Trim(strtmp)
    SumCell = Evaluate(Replace(strtmp, " ", "+"))
    
End Function

**mstubbs** · 12-11-2012, 01:23 AM

Thank you Marcol, I appreciate it. This worked somewhat, the only problem I have now is that I only want the numbers inside the ( ) to be totaled. If I add numbers outside of the ( ) it is still including them in the sum. Any ideas?

Extracting Numbers from text

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Extracting Numbers from text

Re: Extracting Numbers from text

Re: Extracting Numbers from text

Re: Extracting Numbers from text

Re: Extracting Numbers from text

Re: Extracting Numbers from text

Re: Extracting Numbers from text

Re: Extracting Numbers from text

Re: Extracting Numbers from text

Re: Extracting Numbers from text

Re: Extracting Numbers from text

Re: Extracting Numbers from text

Re: Extracting Numbers from text

Re: Extracting Numbers from text

Re: Extracting Numbers from text

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions