×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Formula Advice

thread68-1821540

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.


RE: Formula Advice

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

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

combo

RE: Formula Advice

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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

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.

RE: Formula Advice

(OP)
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.

RE: Formula Advice

(OP)
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.



RE: Formula Advice

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

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

Quote (Cpreston)

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

RE: Formula Advice

(OP)
Hi Combo

Great works perfect now many thanks for the assist.

RE: Formula Advice

From MS help:
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.
And:
If start_num is greater than the length of text, MID returns "" (empty text).


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

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

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

RE: Formula Advice

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
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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close