Trim text to get a number

[zarathustra]

Hey guys,

hope you all are well.

My sheet - I got - is a big mess. in the cells there is a full sentence with some numbers which I want to get, for instance:

TEXT Fee: 123$ MORE TEXT Rate: 456$ MORE TEXT

The sentences never have the same length and never the same text

Main Problem: How can I get only the value after fee and rate?

Secondary Problem: After the second MORE TEXT I have some individual fees, TV 56$ cables 40$ = 96$, computer 1000$, phone 200$ = 1200$

I would also like to extract the numbers after the "=".

Help is much appreciated!

T

 

[Rick Rothstein]

The biggest problem is that the fee is some times placed at the and and some times at the beginning.

Is there a special order that you want the numbers in or do you just want the numbers in the order that they appear in the text? If you want them in a special order, then I think you will need to post several examples showing the various ways the text can look then also show us the result you want for each one of them.

 

[zarathustra]

Hi Rick,

I would like to have the numbers in the next Cell. Text in Cell A1, Fee Value in B1, Computer Value in C1 and TV Value in E1.

 

[zarathustra]

Using your formula as a base, if we change the colons to equal signs and split on equal signs instead of columns, then your modified formula will find all of the numbers (note that I added IFERROR to have columns with no split out data show "" instead of an error)...

=IFERROR(-LOOKUP(1,-LEFT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE($A1,":","="),"=",REPT(" ",99),COLUMNS($A:A)),99)),ROW($1:$15))),"")

I am just very curious, could you please explain the formula as I am trying to understand for myself - even though it didn't work.

From "Rept" on I don't understand what it does.

Thanks very much!

 

[zarathustra]

Are my previous examples enough or should I provide new ones from the file?