Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

left extract after first hyphen and/or first space

0

I need to extract a base number from a list.  Some of them have hyphens and some have spaces.  I need to extract left of the first hyphen and if no hypen, left of the first space and if no hypen or space, the just the content of the cell.

from the item below, i need to show the base 110 then the 1102.  I tried 

=LEFT($F39,IFERROR(FIND("-",$F39),IFERROR(FIND(" ",$F39),999)-1))

but then the base shows the hyphen ..... 

110-

and I'm not sure what I'm doing wrong.  Hopefully someone can help and then I can palm smack my face and say "OMG".

item examples below

110-32 JD 110-34 JD 110-36 JD 1102 D

1102 K

1134

excel
posted by: LNN (rep: 2) on Tue Dec 21, 2021 at 4:36 pm
Answer
Discuss

Answers

0
Selected Answer

Hi LNN and welcome to the Forum

Find will locate the position of the search text (e.g. "-" in the string). It will be 4 in 110-32 JD but you only want the first 3 characters so need to subtract 1 from the FIND result.

Your formula removes the 1 from the wrong place. It should read (change in bold):

=LEFT($F39,IFERROR(FIND("-",$F39),IFERROR(FIND(" ",$F39),999))-1)
not -1))

Hope this helps.

posted by: John_Ru (rep: 6142) on Wed Dec 22, 2021 at 3:42 am
Discuss

Discussion

If you heard a sound, that was not a palm smack, that was my forehead banging on my desk - thanks a bunch John - it worked perfectly.
LNN (rep: 2) Dec 22, '21 at 10:52 am
Glad it's fixed now and thanks for selecting my answer 
John_Ru (rep: 6142) Dec 22, '21 at 12:01 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login