Extract numerical value from text string after : within text string

[Range]

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.

=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)))

 

[shg]

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

 

[Rick Rothstein]

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.

=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]

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
	A	B	C	D
1	Text123 : String is 34	34	34	68
2	Text123 : String is 34	34	34	0
Sheet1

Cell Formulas
Range	Formula
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]

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

	A	B	C	D
1	Text123 : String is 34	34	34	68
2	Text123 : String is 34	34	34	0

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula 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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks again

 

[hiker95]

Range,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.