Find - Replace help help

[edmos1]

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

[kraljb]

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

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

[CLR]

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

[Pete_UK]

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

[edmos1]

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]

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?

[Ron Coderre]

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

[Niek Otten]

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

--
Kind regards,

Niek Otten

"edmos1" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Pete_UK]

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

[edmos1]

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]

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

[Ron Coderre]

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