[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Parse formula, need help

Posted on 2014-01-16
17
Medium Priority
?
331 Views
Last Modified: 2014-01-16
I am trying to parse off of this text string... first everything right of the second "_" and everything left of the file format "."    How would I write a formula that would result in "EA-010" from the following:


Abaca_Brass_EA-010.jpg  needs to = EA-010

Please advise and thanks. -R-
0
Comment
Question by:RWayneH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
17 Comments
 
LVL 7

Expert Comment

by:Steve
ID: 39785416
Depending on if structure of the value...

=MID(A1,FIND("_",A1,FIND("_",A1,1)+1),FIND(".",A1,1)-(FIND("_",A1,FIND("_",A1,1)+1)))
0
 

Author Comment

by:RWayneH
ID: 39785428
could you help explain this formula in english?  -R-
0
 

Author Comment

by:RWayneH
ID: 39785440
plus the result is show a leading "_", which does not need to be there.   _EA-010   -R-
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 31

Expert Comment

by:gowflow
ID: 39785461
Try this
Put this in A1
Abaca_Brass_EA-010.jpg

and put this in B1
=MID(A1,FIND("_",A1,FIND("_",A1)+1)+1,FIND(".",A1)-(FIND("_",A1,FIND("_",A1)+1)+1))


gowflow
0
 

Author Comment

by:RWayneH
ID: 39785477
I want to understand this formula so in the next cell right I can put everything left of the second "_".  =  Abaca_Brass_
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39785507
ok here it is:

MID has a structure of MID(Text,Start Position,Number of Character)
this will extract from the Text or a cell reference at the starting position a certain number of characters.

FIND has the structure FIND(Find Text,Within Text,optional start number)
This will find the position of a certain character within string or a cell reference.

What we do is a double find to get the position of the second _ and increment to 1 this will give us the position of the first character after the second _
FIND("_",A1,FIND("_",A1)+1)+1

then Find(A1,".") will give us the position of the .

then we construct with MID

MID(A1 or the whole text,at position the double find like above,the length is position of the find minus position of the double find)
you get your text !!

Hope above explains it.
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39785515
Put this in C1 it will give you
Abaca_Brass_

=MID(A1,1,FIND("_",A1,FIND("_",A1)+1))


gowflow
0
 
LVL 7

Expert Comment

by:Steve
ID: 39785533
It is just nested formulas.
In a mid statement you would normally have:
Mid(text, start_number, number_char)
So for the text, that's what you're dividing up,

Start number is:
Find the first "_" and use that number(+1) as a start number to look for the second"_" then add 1 so you are starting your MID after the second "_",

Number of characters is:
Find the "." and
 subtract the start number you used to start after the second "_".

This way you are starting after the second "_" and ending just before the ".".
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 39785535
I have put some examples with 2 _ and a point and dragged the formula down. Look at the attached file.

gowflow
parseformula.xls
0
 

Author Comment

by:RWayneH
ID: 39785552
I like to break into pieces:

=MID(A1,FIND("_",A1,FIND("_",A1)+1)+1,
so this first part finds the second "_" in A1. and adds a char to remove the "_" and marks the start point.

FIND(".",A1)-(FIND("_",A1,FIND("_",A1)+1)+1))
and this second part finds the dot, and subtracts it from the start point?

It looked odd that it is finding the start point twice..  I think I am getting this..

So to grab everything left of the second _  (including it)
=LEFT(A1, FIND("_",A1))  ??  -R-
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39785565
NO wrong !!! check my explanation.

so this first part finds the second "_" in A1. and adds a char to remove the "_" and marks the start point.
is this:
FIND(".",A1)-(FIND("_",A1,FIND("_",A1)+1)+1))

This
FIND(".",A1)-(FIND("_",A1,FIND("_",A1)+1)+1))
give you the Length of the text to parse in the formula !!!


You cannot use this formula to get your second part look at my comment above !
gowflow
0
 

Author Comment

by:RWayneH
ID: 39785615
ok I get it now... with the dot at the end... it was confusing me... it work right to left to find.  If I was working left to right would that chg the subtract after the first grp of ()'s in:  FIND(".",A1)-

to a "+"?  or is the hyphen mean from here to here no matter the direction?
It is making sense...  sorry for all the quesitons..   (for understanding).  -R-
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39785823
Sorry I don't understand what you want ? why + ??? can you give an example of what you want to look  I would then explain.
gowflow
0
 
LVL 7

Expert Comment

by:Steve
ID: 39785988
It is working from left to right.
It counts how far in the second "_" is and starts at the next character.

Then it finds the "." and subtracts the first number from it to find the length of what's left.
0
 
LVL 7

Expert Comment

by:Steve
ID: 39785993
The +1's are usually because the number it found the "_" at is not where you want to start but the next character.
0
 

Author Comment

by:RWayneH
ID: 39786089
Ok this makes  perfect sense..  thanks for the explaination.. I have now write a few others based on my new understanding.  -R-
0
 

Author Closing Comment

by:RWayneH
ID: 39786092
EXCELlent!! as always...  Thanks. -R-
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question