Extracting the chars to the left of a " ("

  • Thread starter Thread starter Lauren
  • Start date Start date
L

Lauren

Hello

I was hoping someone could help me out of a bit of a
problem...

I have a list of items with a qty in brackets at the end
of the item listed. From that I need to extract just the
characters to the left of the eg.
"space" + "(8)"
OR
"space" + "(12)"

I did have =LEFT(A1,(LEN($A$1)-4))
but I need it to dynamically allow for 4 OR 5 characters.

Can I use an if statement or is there an easier way or a
different way to attack this monster???

I have hundreds of these listed which change regularly. I
don't want to have to check each formula individually.

eg.
A B
HP rp7546 650MHz (2) | =LEFT(A1,(LEN($A$1)-4))
HP rp7546 650MHz
HP rp7546 650MHz (8) | =LEFT(A2,(LEN($A$2)-4))
HP rp7546 650MHz
HP rp7546 650MHz (12) | =LEFT(A3,(LEN($A$3)-5))
HP rp7546 650MHz
IBM p650 Express 1200MHz (2) | =LEFT(A4,(LEN($A$4)-4))
IBM p650 Express 1200MHz
IBM p650 Express 1200MHz (4) | =LEFT(A5,(LEN($A$5)-4))
IBM p650 Express 1200MHz
IBM p650 Express 1200MHz (12) | =LEFT(A6,(LEN($A$6)-5))
IBM p650 Express 1200MHz

I would be grateful for any assistance
Thank you!
Kind regards,
Lauren
 
Hi Lauren,

Try this...

=LEFT(A1,FIND("(",A1)-1)

I subtract 1 because I have "(" without a space.

Regards,
Kevin
 
=LEFT(A1,LEN(A1)-FIND("(",A1)

Regards
Edwin Ta
(e-mail address removed)

----- Lauren wrote: ----

Hell

I was hoping someone could help me out of a bit of a
problem..

I have a list of items with a qty in brackets at the end
of the item listed. From that I need to extract just the
characters to the left of the eg.
"space" + "(8)"
OR
"space" + "(12)

I did have =LEFT(A1,(LEN($A$1)-4)
but I need it to dynamically allow for 4 OR 5 characters

Can I use an if statement or is there an easier way or a
different way to attack this monster???

I have hundreds of these listed which change regularly. I
don't want to have to check each formula individually

eg
A
HP rp7546 650MHz (2) | =LEFT(A1,(LEN($A$1)-4)
HP rp7546 650MH
HP rp7546 650MHz (8) | =LEFT(A2,(LEN($A$2)-4)
HP rp7546 650MH
HP rp7546 650MHz (12) | =LEFT(A3,(LEN($A$3)-5)
HP rp7546 650MH
IBM p650 Express 1200MHz (2) | =LEFT(A4,(LEN($A$4)-4)
IBM p650 Express 1200MH
IBM p650 Express 1200MHz (4) | =LEFT(A5,(LEN($A$5)-4)
IBM p650 Express 1200MH
IBM p650 Express 1200MHz (12) | =LEFT(A6,(LEN($A$6)-5)
IBM p650 Express 1200MH

I would be grateful for any assistanc
Thank you
Kind regards
Laure
 
Thank you Kevin!
I put a space in with the " (" and left the -1 so that my
vlookup could work. If I don't put the space in it leaves
it on my server.

Works like a bought one!
Thanks again
Lauren
 
Hi Edwin
I don't know if i typed something in wrong (possible), but
my return was only HP

Thank you for your assistance!
Lauren :)
 
Lauren,

Just a quick comment...you could have left NO Space, and simply had -2
(instead of minus 1). I like using minus a digit as opposed to spaces
because it "jumps out" more. I have to look carefully to see a space. So
when debugging or later understanding, I find using numbers rather than
spaces preferential. Just my two cents.

Regards,
Kevin
 
Back
Top