Formula Advice 1

Cpreston · May 22, 2023

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 · May 22, 2023

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 · May 22, 2023

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,
_{Just traded in my OLD subtlety...

for a NUance!}
"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 · May 23, 2023

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 · May 23, 2023

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 · May 23, 2023

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 · May 23, 2023

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]

Cpreston said:
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 · May 23, 2023

Hi Combo

Great works perfect now many thanks for the assist.

combo · May 23, 2023

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 · May 23, 2023

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 · Jun 3, 2023

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Formula Advice 1

Cpreston

MIS

combo

Technical User

SkipVought

Programmer

pmonett

Programmer

Cpreston

MIS

Cpreston

MIS

combo

Technical User

Cpreston

MIS

combo

Technical User

strongm

MIS

fredericofonseca

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor