# Extract numerical value from text string after : within text string

#### Range

##### Board Regular
Cell A1 = "Text123 : String is 34"

I am trying to extract the 34 from the text string in A1 but I do not want to extract the 123....

I tried the below. Failed miserably.

Code:
``=MID((MID(A3,FIND(":",A3)+3,LEN(A3))),MATCH(TRUE,ISNUMBER(1*MID((MID(A3,FIND(":",A3)+3,LEN(A3))),ROW(\$1:\$101),1)),0),COUNT(1*MID((MID(A3,FIND(":",A3)+3,LEN(A3))),ROW(\$1:\$101),1)))``

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### shg

##### MrExcel MVP
=mid(substitute(a1, " ", "|", len(a1) - len(substitute(a1, " ", ""))), find("|", substitute(a1, " ", "|", len(a1) - len(substitute(a1, " ", "")))) + 1, 99)

#### Rick Rothstein

##### MrExcel MVP
Cell A1 = "Text123 : String is 34"

I am trying to extract the 34 from the text string in A1 but I do not want to extract the 123....

I tried the below. Failed miserably.

Code:
``=MID((MID(A3,FIND(":",A3)+3,LEN(A3))),MATCH(TRUE,ISNUMBER(1*MID((MID(A3,FIND(":",A3)+3,LEN(A3))),ROW(\$1:\$101),1)),0),COUNT(1*MID((MID(A3,FIND(":",A3)+3,LEN(A3))),ROW(\$1:\$101),1)))``
One example is usually not enough to clue us into what you data looks like... a representative set of samples showing the various ways in which the text can appear would be better. If the number you want is always at the end of the text (like your example shows), then you can use this formula to get it...

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

#### hiker95

##### Well-known Member
Range,

If you want to extract the number to do some calculations (the YELLOW cells, cells B1, and C1), how about something like this?

Excel 2007
ABCD
1Text123 : String is 34343468
2Text123 : String is 3434340
Sheet1
Cell Formulas
RangeFormula
B1=0+TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))
B2=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))
C1=0+MID(SUBSTITUTE(A1, " ", "|", LEN(A1) - LEN(SUBSTITUTE(A1, " ", ""))), FIND("|", SUBSTITUTE(A1, " ", "|", LEN(A1) - LEN(SUBSTITUTE(A1, " ", "")))) + 1, 99)
C2=MID(SUBSTITUTE(A2, " ", "|", LEN(A2) - LEN(SUBSTITUTE(A2, " ", ""))), FIND("|", SUBSTITUTE(A2, " ", "|", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))) + 1, 99)
D1=SUM(B1:C1)
D2=SUM(B2:C2)

#### Range

##### Board Regular
Wow Guys, Thank you soo much. This information is soo useful to me and currently I dont have time to fully break it down but over the next couple of months I will be and learning to do this myself properly. In the meantime I will just be copying and pasting your help - thanks soo much again.

One example is usually not enough to clue us into what you data looks like... a representative set of samples showing the various ways in which the text can appear would be better. If the number you want is always at the end of the text (like your example shows), then you can use this formula to get it...

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

Appreciate the feedback Rick, I have lots of outstanding issues on a spreadsheet I am working on for a friend. I will provide better detailed first posts in future.

Range,

If you want to extract the number to do some calculations (the YELLOW cells, cells B1, and C1), how about something like this?

Excel 2007
ABCD
1Text123 : String is 34343468
2Text123 : String is 3434340

</tbody>
Sheet1

Worksheet Formulas
CellFormula
B1=0+TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))
C1=0+MID(SUBSTITUTE(A1, " ", "|", LEN(A1) - LEN(SUBSTITUTE(A1, " ", ""))), FIND("|", SUBSTITUTE(A1, " ", "|", LEN(A1) - LEN(SUBSTITUTE(A1, " ", "")))) + 1, 99)
D1=SUM(B1:C1)
B2=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))
C2=MID(SUBSTITUTE(A2, " ", "|", LEN(A2) - LEN(SUBSTITUTE(A2, " ", ""))), FIND("|", SUBSTITUTE(A2, " ", "|", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))) + 1, 99)
D2=SUM(B2:C2)

</tbody>

<tbody>
</tbody>
Thanks again

#### hiker95

##### Well-known Member
Range,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

Replies
9
Views
342
Replies
4
Views
821
Replies
7
Views
578
Replies
7
Views
265
Replies
2
Views
200

### Forum statistics

1,195,848
Messages
6,011,946
Members
441,656
Latest member
oo3 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back