Formula Advice 1

[Cpreston]

Hi

I have a spreadsheet that as data in column H called OldValue. Then entry's could start with Total Price or Unit Price.

an example of the Total one is
Total Price=91.36
I just want to get the price out of this , if I use the following formula =MID(H2,13,LEN(H2)-10) I get 91.36.

However the Unit price looks like the below and all I want is the first 865.86.
Unit Price=865.86,Per ID=1,Total Price=865.86,Quantity=1.0000,InputPer ID=1,Discount1=0.00,Discount2=0.00,Discount3=0.00,Discount4=0.00

I adapted the same formula to =MID(H9,12,LEN(H9)-129) which in this case gave ,e 865.86, however if it is over a thousand I run into an issue where it would give me 1449.06,.

So I have 2 issues

1. Can I do an IF function to say if it starts with total then use this formula else if it starts with Unit use the other formula.
2. How do I get it to extract the number only if it goes to the thousands and not show the comma.

Thanks in advance.

 

[combo]

If you want to find text between first '=' and first ',' if exists, else till the end of string, you can:

[tt]=MID(H2,SEARCH("=",H2)+1,IF(ISERROR(SEARCH(",",H2),LEN(H2)+1,SEARCH(",",H2))-SEARCH("=",H2)-1)[/tt]

combo

 

[SkipVought]

Just as an alternative method, not quite as slick as combo's excellent solution, check out the Data>Text to Columns with a COMMA delimiter and DO NOT IMPORT option for all but the first "column".

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[pmonett]

If you're importing from a csv file, I'd first load it into a good text file editor (i.e. Notepad++) and do a Search & Replace to remove all occurences of anything before (and including) the = sign.
Then you make sure the first line has the titles, and then you can import the data into Excel.
Then, if possible, you should contact the person responsible for making the file and ask him to stop sending you useless text. A csv file is supposed to contain data, not labels. That's what the header line is for.

I've got nothing to hide, and I demand that you justify what right you have to ask.

 

[Cpreston]

Hi

Combo I tried you formula but it just stayed as a formula and gave no results. I probably do not understand what it is doing but will examine it more.

Pmonett it is not a CSV file but a direct link to a SQL table which our ERP system generates, heaven knows why it shows like it does but not very useable I agree.

Thanks for the replies appreciated.

 

[Cpreston]

For the Total Price=250.00 I used the formula =MID(H2,13,SEARCH("=",H2)+1) and I get 91.36

If I apply this to the line

Unit Price=1153.60,Per ID=1,Total Price=1153.60,Quantity=1.0000,InputPer ID=1,Discount1=0.00,Discount2=0.00,Discount3=0.00,Discount4=0.00

I get

153.60,Per I

I have tried to play with the formula sent by Combo but cannot get that working.

 

[combo]

Probably the cell is formatted as text, change the format to general and re-enter the formula.

I revised the formula, the proper syntax:
[tt]=MID(H2,SEARCH("=",H2)+1,IF(ISERROR(SEARCH(",",H2)),LEN(H2)+1,SEARCH(",",H2))-SEARCH("=",H2)-1)[/tt]

I probably do not understand what it is doing

It's simple, SEARCH finds a position of searched character, either '=' or ','. If not found, error is returned (as missing comma in 'Total Price=91.36'), in this case total string length+1 is returned.
To debug the formula, start with analysing what SEARCH("=",H2) and SEARCH(",",H2) (or LEN(H2)+1 if no comma) return for your string.
The formula returns string, VALUE function can convert it to number.

combo

 

[Cpreston]

Hi Combo

Great works perfect now many thanks for the assist.

 

[combo]

From MS help:
[pre]MID(text,start_num,num_chars)
Text is the text string containing the characters you want to extract.
Start_num is the position of the first character you want to extract in text. The first character in text has start_num 1, and so on.
Num_chars specifies the number of characters you want MID to return from text.[/pre]
And:
[pre]If start_num is greater than the length of text, MID returns "" (empty text).[/pre]

With =MID(H2,13,SEARCH("=",H2)+1) you fix the starting point (13). Next take a string with length equal to the position of '='+1 (or shorter) - the pure SEARCH function is not the best to calculate the length of text you want to return.

combo

 

[strongm]

Latest versions of Excel 365 have TEXTBEFORE and TEXTAFTER functions, which allow a simpler version of Combo's solution, something like:

[tt]=TEXTBEFORE(TEXTAFTER(H2, "="), ",")[/tt]

 

[fredericofonseca]

if you use SQL to query that ERP system then why not do that split on the sql that retrieved the data - that way you have full control of what you get, you can get it in a readly usable number format, and you can even retrieve all other columns at same time on their own individual columns.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886