## Formula Advice

## Formula Advice

(OP)

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.

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.

## RE: Formula Advice

=MID(H2,SEARCH("=",H2)+1,IF(ISERROR(SEARCH(",",H2),LEN(H2)+1,SEARCH(",",H2))-SEARCH("=",H2)-1)

combo

## RE: Formula Advice

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!## RE: Formula Advice

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.## RE: Formula Advice

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.

## RE: Formula Advice

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.

## RE: Formula Advice

I revised the formula, the proper syntax:

=MID(H2,SEARCH("=",H2)+1,IF(ISERROR(SEARCH(",",H2)),LEN(H2)+1,SEARCH(",",H2))-SEARCH("=",H2)-1)

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

## RE: Formula Advice

Great works perfect now many thanks for the assist.

## RE: Formula Advice

And:

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

## RE: Formula Advice

=TEXTBEFORE(TEXTAFTER(H2, "="), ",")

## RE: Formula Advice

Regards

Frederico Fonseca

SysSoft Integrated Ltd

www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?

FAQ181-2886: How can I maximize my chances of getting an answer?