Extracting second number from text thread

[Ronak Parekh]

Hello,

I have more than 1000 text string as below;

Backfill Partial Audit Error Fixes for Central Bank of Belize 4558440 545614

Now in the end of the string there are two different values and both of them are to be used for different purpose. "4558440" and "545614"

Thus is it possible to extract those two values in different cells using right function. Challenge over here is that both the numbers would have different numbers of digit in different rows.

 

[eibi]

Ronak,

Welcome to the forums.

Assuming your original string is in A1

I would paste the following formula in B1:

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",256)),256+256)),FIND(" ",TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",256)),256+256)))-1)

And the following formula in C1:

=MID(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",256)),256+256)),FIND(" ",TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",256)),256+256)))+1,256)

Does this achieve your objective?

 

[Ronak Parekh]

this worked.. thanks a ton Eibi

 

[eibi]

Apologies for a typo in my original reply. I've corrected it, above.

 

[Deepak]

Hi,

There's are lot's of way to get it.

B1 =TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99*2),99))
C1 =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

 

[Deepak]

I have did something here...

http://forum.chandoo.org/threads/need-clarification-on-the-excel-formula.23026/#post-139265

Updated xl is also attached herewith...