so vlookup needs an exact search to look up from:

if I have a value = "aaa-US"
but I am trying to look up a value for "aaa"
I would have to systematically strip off the "-US"
I know I can do a find "-US" or crtl F
but any ideas on how I can create a formlula so I would have
Column A Column B
aaa-US aaa

Thanks D


--
edmos1
------------------------------------------------------------------------
edmos1's Profile: http://www.excelforum.com/member.php...o&userid=31903
View this thread: http://www.excelforum.com/showthread...hreadid=516304

=left(A1,len(A1)-3)

The 3 being the number of Characters in "-US"


--
kraljb
------------------------------------------------------------------------
kraljb's Profile: http://www.excelforum.com/member.php...fo&userid=9955
View this thread: http://www.excelforum.com/showthread...hreadid=516304

In B1 put

=LEFT(A1,3)

Vaya con Dios,
Chuck, CABGx3



"edmos1" wrote:


so vlookup needs an exact search to look up from:

if I have a value = "aaa-US"
but I am trying to look up a value for "aaa"
I would have to systematically strip off the "-US"
I know I can do a find "-US" or crtl F
but any ideas on how I can create a formlula so I would have
Column A Column B
aaa-US aaa

Thanks D


--
edmos1
------------------------------------------------------------------------
edmos1's Profile: http://www.excelforum.com/member.php...o&userid=31903
View this thread: http://www.excelforum.com/showthread...hreadid=516304

if you always want to take the right hand 3 characters away, then this
formula will do it in B1:

=LEFT(A1,LEN(A1)-3)

Then copy the formula down. Hope this helps.

Pete

What if all my data does not have the "-US"
if I have
aaa-US
bbb-ME
ccc

I would want to display
aaa
bbb
ccc

any thoughts?


--
edmos1
------------------------------------------------------------------------
edmos1's Profile: http://www.excelforum.com/member.php...o&userid=31903
View this thread: http://www.excelforum.com/showthread...hreadid=516304

What if all my data does not have the "-US"
if I have
aaa-US
bbb-ME
ccc

I would want to display
aaa
bbb
ccc

any thoughts?


--
edmos1
------------------------------------------------------------------------
edmos1's Profile: http://www.excelforum.com/member.php...o&userid=31903
View this thread: http://www.excelforum.com/showthread...hreadid=516304

Try this:

For text in A1

B1: =IF(COUNTIF(A1,"*-*"),LEFT(A1,FIND("-",A1)-1),A1)

If the text in A1 contains a dash (-), the formula takes the characters
to the left of the dash. Otherwise, it takes all of the text.

Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=516304

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

--
Kind regards,

Niek Otten

"edmos1" wrote in
message ...

What if all my data does not have the "-US"
if I have
aaa-US
bbb-ME
ccc

I would want to display
aaa
bbb
ccc

any thoughts?


--
edmos1
------------------------------------------------------------------------
edmos1's Profile:
http://www.excelforum.com/member.php...o&userid=31903
View this thread: http://www.excelforum.com/showthread...hreadid=516304

If you always want the first 3 characters from the left, then the
formula that Chuck gave you will do this, i.e.:

=LEFT(A1,3)

Hope this helps.

Pete

hmmmm ok

how about if it was aaa-41-US

I would like to find aaa-41

I have played around with a nested IF(replace) statement, but I have to
know the placement

could I create a IF (find (replace) or am I over confusing this?

thanks
D


--
edmos1
------------------------------------------------------------------------
edmos1's Profile: http://www.excelforum.com/member.php...o&userid=31903
View this thread: http://www.excelforum.com/showthread...hreadid=516304

Thank Niek,
I modified yours a bit,

=IF(ISERROR(FIND("US",A1)),A1,LEFT(A1,FIND("US",A1 )-2))

seems to work perfectly

I hav not used the iserror to much, looks like I need to brush off my
excel skills a bit

thank you all
D


--
edmos1
------------------------------------------------------------------------
edmos1's Profile: http://www.excelforum.com/member.php...o&userid=31903
View this thread: http://www.excelforum.com/showthread...hreadid=516304

Ok...I think we can accomodate that:

For text in A1 that may, or may not, contain dashes.

This formula returns all of the characters to the left of the last
instance of a dash:
B1:
=IF(COUNTIF(A1,"*-*"),LEFT(A1,LOOKUP(LEN(A1),FIND("-",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))) )-1),A1)


Does that do what you want?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=516304