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

 

[zarathustra]

Regard the secondary problem: The amount of products variety. My idea was to define first "=" and second "=" to get the values.
Something like if "press" in sentence then first value after "=" etc.

Is this even possible or are the numbers also formatted as text?

 

[AlKey]

For the Main problem:
Enter formula in B1 and copy across to C1

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

Book1
	A	B	C
1	TEXT Fee: 123$ MORE TEXT Rate: 456$ MORE TEXT	123	456
Sheet1

For your Secondary problem you need to make it more clear of what you want.

 

[hiker95]

Zarathustra,

Welcome to the MrExcel forum.


I see that AlKey has given you a formula.


1. Would you be interested in a macro solution?


We can not tell where your raw data is located, sheet name(s), cells, cell formatting, cell formulae, rows, columns, and, we can not tell where the results should be, sheet name, cells, cell formatting, cell formulae, rows, columns.


If you have strings/sentences, then screenshots will not work.


2. Can we see your actual raw data workbook/worksheet containing the strings/sentences?

3. Can we see what the results (manually formatted by you) should look like?


You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[Rick Rothstein]

For the Main problem:
Enter formula in B1 and copy across to C1

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

Book1
	A	B	C
1	TEXT Fee: 123$ MORE TEXT Rate: 456$ MORE TEXT	123	456
Sheet1

For your Secondary problem you need to make it more clear of what you want.

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))),"")

 

[AlKey]

Hi Rick, Just wondering: replacing one delimiter with another? I don't understand the purpose. I know, I probably missing something.

 

[Rick Rothstein]

Hi Rick, Just wondering: replacing one delimiter with another? I don't understand the purpose. I know, I probably missing something.

The modified formula handles both requests by the OP. Let's say this is the text in the cell...

[table="width: 500"]
[tr]
	[td]TEXT Fee: 123$ MORE TEXT Rate: 456$ MORE TEXT TV 56$ cables 40$ = 96$, computer 1000$, phone 200$ = 1200$[/td]
[/tr]
[/table]

Using your original formula... it would only find the numbers after the colons. By changing the colons to equal signs and changing your formulas delimiter to an equal sign, all the numbers the OP wanted would be returned. Place the above text in cell A1 and put the modified formula I posted in cell B1 and drag the formula across, say, 5 cells... the four numbers the OP wanted from the text will be displayed in cells B1:E1... cell F1 will have the formula but return the empty text string ("") because there is no 5th number in the text (meeting the OP's requirement) to display. I should point out to the OP at this point that if his text can be very long, he should change the two 99's in the formula to something longer, maybe 200 or even 300.

 

[AlKey]

Oh, you didn't mention that you put both stings together. Now that make sense. Thank you.

 

[zarathustra]

Hey guys, thanks very much. I tried the formula it worked not very good as I didn't explain my problem properly. Unfortunately I can't upload the spreadsheet or data but I can give you some examples.

Text
Text
Fee@ $100
Tv $1 x 9 = $18
Computer $2 x 63 = $126

Then others look like this:

text Tv $1 x 9 = $18 Computer $2 x 63 = $126 Fee@ 2,982

or even like this:

text Tv $1 x 9 = $18 Computer $2 x 63 = $126Fee@ 2,982

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

I have never ever had text problems in excel so I'm really struggling there.

 

[zarathustra]

The numbers should be formatted as text...