How to extract the right most numbers from a text string?

[technomarketing]

Hello,
I need to extract numbers from a text string. The numbers appear to the right of the string. There are actually two numbers there. One is the count and the other is the percent. I definitely appreciate any help or advice. The data is formatted like this: [TABLE="width: 800"]
<tbody>[TR]
[TD="width: 800"]E20 No Place Like Home 140 12.79%

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Is it fair to say you want the value to the right of the LAST space?

 

[technomarketing]

Yes. That would work.

 

[Jonmo1]

Try

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

Note, this also captures the % symbol at the end..

 

[Rick Rothstein]

Hello,
I need to extract numbers from a text string. The numbers appear to the right of the string. There are actually two numbers there. One is the count and the other is the percent. I definitely appreciate any help or advice. The data is formatted like this: [TABLE="width: 800"]
<tbody>[TR]
[TD="width: 800"]E20 No Place Like Home 140 12.79%

[/TD]
[/TR]
</tbody>[/TABLE]

I read your post as asking for both of those ending numbers, so put this formula in F20 and copy it across to G20...

=TRIM(LEFT(RIGHT(SUBSTITUTE($E20," ",REPT(" ",99)),(3-COLUMNS($F20:F20))*99),99))

 

[technomarketing]

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)) returned the full text string of "E20 No Place Like Home 140 12.79%." Is there a chance I entered it wrong? I'm definitely open to any other suggestions.

 

[Jonmo1]

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)) returned the full text string of "E20 No Place Like Home 140 12.79%."

sounds like your spaces aren't really spaces.

What does this return
=CODE(MID(A1,4,1))

Where 4 is the position # of the first one of those 'spaces' (right after E20)

 

[technomarketing]

=CODE(MID(A1,4,1)) returned 32

 

[Jonmo1]

That doesn't make sense..

The formula I posted works fine for me.
Ricks does as well, though we both interpraited your desired result differently.
Mine will get the 12.79%, Ricks gets the 140

If they return the entire original string, it means that string contains no spaces.
But the test with the code function says they are.. (or at least the one right after E20 was)
Or did you write E20 as the cell that contains "No Place Like Home 140 12.79%"

Either way, it should work.

 

[technomarketing]

E20 was part of the string. I wonder if there is a formatting issue. I had to copy and past the data from a PDF document into Excel. I will go back in and try both formula's again. Neither of them worked the first time.