Formula to return part of a text string
 

I have a clomun of cells with various codes in e.g:

Column A
42NAFF16-43
927PLY833-16FF
16ARBY1-1

etc..

The only common factor in all these references is the dash symbol. I am
looking to return in another column just the figures after the dash e.g:

Column B
43
16FF
1

I'm stuck as to what formula to use as I'm only used to chopping off a fixed
number of digits! Any help is greatly appreciated.

Formula to return part of a text string
 

With your data in column A, put this in B1 ad copy down........

=MID(A1,FIND("-",A1,1),99)

Vaya con Dios,
Chuck, CABGx3



"luvthavodka" wrote:

I have a clomun of cells with various codes in e.g:

Column A
42NAFF16-43
927PLY833-16FF
16ARBY1-1

etc..

The only common factor in all these references is the dash symbol. I am
looking to return in another column just the figures after the dash e.g:

Column B
43
16FF
1

I'm stuck as to what formula to use as I'm only used to chopping off a fixed
number of digits! Any help is greatly appreciated.

Formula to return part of a text string
 

If the hyphen appears only once in the text, then use this:

=RIGHT(B2,LEN(B2)-FIND("-",B2))


Hope this helps,
Dom



luvthavodka wrote:
I have a clomun of cells with various codes in e.g:

Column A
42NAFF16-43
927PLY833-16FF
16ARBY1-1

etc..

The only common factor in all these references is the dash symbol. I am
looking to return in another column just the figures after the dash e.g:

Column B
43
16FF
1

I'm stuck as to what formula to use as I'm only used to chopping off a fixed
number of digits! Any help is greatly appreciated.

Formula to return part of a text string
 

Thanks for this, however it is returning -43GF, rather than 43GF.

How can I expand on this formula to also remove the "-"?

Many thanks

"CLR" wrote:

With your data in column A, put this in B1 ad copy down........

=MID(A1,FIND("-",A1,1),99)

Vaya con Dios,
Chuck, CABGx3



"luvthavodka" wrote:

I have a clomun of cells with various codes in e.g:

Column A
42NAFF16-43
927PLY833-16FF
16ARBY1-1

etc..

The only common factor in all these references is the dash symbol. I am
looking to return in another column just the figures after the dash e.g:

Column B
43
16FF
1

I'm stuck as to what formula to use as I'm only used to chopping off a fixed
number of digits! Any help is greatly appreciated.

Formula to return part of a text string
 

Sorry, this should do it for you.........

=MID(A1,FIND("-",A1,1)+1,99)

Vaya con Dios,
Chuck, CABGx3



"luvthavodka" wrote:

Thanks for this, however it is returning -43GF, rather than 43GF.

How can I expand on this formula to also remove the "-"?

Many thanks

"CLR" wrote:

With your data in column A, put this in B1 ad copy down........

=MID(A1,FIND("-",A1,1),99)

Vaya con Dios,
Chuck, CABGx3



"luvthavodka" wrote:

I have a clomun of cells with various codes in e.g:

Column A
42NAFF16-43
927PLY833-16FF
16ARBY1-1

etc..

The only common factor in all these references is the dash symbol. I am
looking to return in another column just the figures after the dash e.g:

Column B
43
16FF
1

I'm stuck as to what formula to use as I'm only used to chopping off a fixed
number of digits! Any help is greatly appreciated.

Formula to return part of a text string
 

Hi,
This is helpful, but have something slightly different i could use some help
with. Two different situations.

One, is there a way to combine this with the "LEFT" Command? In my example,
i am looking to get the city and state, but not include the dash. The dash
is my common separater in all my values.

City, ST - Market Number
Looking to Return "City, St"

In my second scenario, I want to use the same command except have it find
the second dash and return everything to the left of the second dash.

WWWW-FM-City, St
Looking to return "WWWW-FM"

In both situations, i can't use the straight LEFT Command because the list
has different number of characters. I might be able to easily replace the
second dash with a different common character, but this formula includes the
common character and i would prefer not to see it.

Thanks - Any help is appreciated.
Rugdoody



"CLR" wrote:

Sorry, this should do it for you.........

=MID(A1,FIND("-",A1,1)+1,99)

Vaya con Dios,
Chuck, CABGx3



"luvthavodka" wrote:

Thanks for this, however it is returning -43GF, rather than 43GF.

How can I expand on this formula to also remove the "-"?

Many thanks

"CLR" wrote:

With your data in column A, put this in B1 ad copy down........

=MID(A1,FIND("-",A1,1),99)

Vaya con Dios,
Chuck, CABGx3



"luvthavodka" wrote:

I have a clomun of cells with various codes in e.g:

Column A
42NAFF16-43
927PLY833-16FF
16ARBY1-1

etc..

The only common factor in all these references is the dash symbol. I am
looking to return in another column just the figures after the dash e.g:

Column B
43
16FF
1

I'm stuck as to what formula to use as I'm only used to chopping off a fixed
number of digits! Any help is greatly appreciated.

Formula to return part of a text string
 

For your first scenario

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


and for your second


=LEFT(A2,FIND("^^",SUBSTITUTE(A2,"-","^^",2))-1)



--


Regards,


Peo Sjoblom


--


Regards,


Peo Sjoblom

"Rugdoody" wrote in message
...
Hi,
This is helpful, but have something slightly different i could use some
help
with. Two different situations.

One, is there a way to combine this with the "LEFT" Command? In my
example,
i am looking to get the city and state, but not include the dash. The
dash
is my common separater in all my values.

City, ST - Market Number
Looking to Return "City, St"

In my second scenario, I want to use the same command except have it find
the second dash and return everything to the left of the second dash.

WWWW-FM-City, St
Looking to return "WWWW-FM"

In both situations, i can't use the straight LEFT Command because the list
has different number of characters. I might be able to easily replace the
second dash with a different common character, but this formula includes
the
common character and i would prefer not to see it.

Thanks - Any help is appreciated.
Rugdoody



"CLR" wrote:

Sorry, this should do it for you.........

=MID(A1,FIND("-",A1,1)+1,99)

Vaya con Dios,
Chuck, CABGx3



"luvthavodka" wrote:

Thanks for this, however it is returning -43GF, rather than 43GF.

How can I expand on this formula to also remove the "-"?

Many thanks

"CLR" wrote:

With your data in column A, put this in B1 ad copy down........

=MID(A1,FIND("-",A1,1),99)

Vaya con Dios,
Chuck, CABGx3



"luvthavodka" wrote:

I have a clomun of cells with various codes in e.g:

Column A
42NAFF16-43
927PLY833-16FF
16ARBY1-1

etc..

The only common factor in all these references is the dash symbol.
I am
looking to return in another column just the figures after the dash
e.g:

Column B
43
16FF
1

I'm stuck as to what formula to use as I'm only used to chopping
off a fixed
number of digits! Any help is greatly appreciated.

Formula to return part of a text string
 

=LEFT(F1,FIND("-",F1,1)-1) for the first case

=LEFT(F7,FIND("-",F7,FIND("-",F7,1)+1)-1) for the second

Vaya con Dios,
Chuck, CABGx3



"Rugdoody" wrote:

Hi,
This is helpful, but have something slightly different i could use some help
with. Two different situations.

One, is there a way to combine this with the "LEFT" Command? In my example,
i am looking to get the city and state, but not include the dash. The dash
is my common separater in all my values.

City, ST - Market Number
Looking to Return "City, St"

In my second scenario, I want to use the same command except have it find
the second dash and return everything to the left of the second dash.

WWWW-FM-City, St
Looking to return "WWWW-FM"

In both situations, i can't use the straight LEFT Command because the list
has different number of characters. I might be able to easily replace the
second dash with a different common character, but this formula includes the
common character and i would prefer not to see it.

Thanks - Any help is appreciated.
Rugdoody



"CLR" wrote:

Sorry, this should do it for you.........

=MID(A1,FIND("-",A1,1)+1,99)

Vaya con Dios,
Chuck, CABGx3



"luvthavodka" wrote:

Thanks for this, however it is returning -43GF, rather than 43GF.

How can I expand on this formula to also remove the "-"?

Many thanks

"CLR" wrote:

With your data in column A, put this in B1 ad copy down........

=MID(A1,FIND("-",A1,1),99)

Vaya con Dios,
Chuck, CABGx3



"luvthavodka" wrote:

I have a clomun of cells with various codes in e.g:

Column A
42NAFF16-43
927PLY833-16FF
16ARBY1-1

etc..

The only common factor in all these references is the dash symbol. I am
looking to return in another column just the figures after the dash e.g:

Column B
43
16FF
1

I'm stuck as to what formula to use as I'm only used to chopping off a fixed
number of digits! Any help is greatly appreciated.

Formula to return part of a text string
 

Hi

For the first question
=LEFT(A1,FIND("-",A1)-1)
For the second
=LEFT(A1,FIND("*",SUBSTITUTE(A1,"-","*",2))-1)

--
Regards
Roger Govier

"Rugdoody" wrote in message
...
Hi,
This is helpful, but have something slightly different i could use some
help
with. Two different situations.

One, is there a way to combine this with the "LEFT" Command? In my
example,
i am looking to get the city and state, but not include the dash. The
dash
is my common separater in all my values.

City, ST - Market Number
Looking to Return "City, St"

In my second scenario, I want to use the same command except have it find
the second dash and return everything to the left of the second dash.

WWWW-FM-City, St
Looking to return "WWWW-FM"

In both situations, i can't use the straight LEFT Command because the list
has different number of characters. I might be able to easily replace the
second dash with a different common character, but this formula includes
the
common character and i would prefer not to see it.

Thanks - Any help is appreciated.
Rugdoody



"CLR" wrote:

Sorry, this should do it for you.........

=MID(A1,FIND("-",A1,1)+1,99)

Vaya con Dios,
Chuck, CABGx3



"luvthavodka" wrote:

Thanks for this, however it is returning -43GF, rather than 43GF.

How can I expand on this formula to also remove the "-"?

Many thanks

"CLR" wrote:

With your data in column A, put this in B1 ad copy down........

=MID(A1,FIND("-",A1,1),99)

Vaya con Dios,
Chuck, CABGx3



"luvthavodka" wrote:

I have a clomun of cells with various codes in e.g:

Column A
42NAFF16-43
927PLY833-16FF
16ARBY1-1

etc..

The only common factor in all these references is the dash symbol.
I am
looking to return in another column just the figures after the dash
e.g:

Column B
43
16FF
1

I'm stuck as to what formula to use as I'm only used to chopping
off a fixed
number of digits! Any help is greatly appreciated.

Formula to return part of a text string
 

Perhaps you should experiment a bit using the same find idea within the LEFT
formula.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rugdoody" wrote in message
...
Hi,
This is helpful, but have something slightly different i could use some
help
with. Two different situations.

One, is there a way to combine this with the "LEFT" Command? In my
example,
i am looking to get the city and state, but not include the dash. The
dash
is my common separater in all my values.

City, ST - Market Number
Looking to Return "City, St"

In my second scenario, I want to use the same command except have it find
the second dash and return everything to the left of the second dash.

WWWW-FM-City, St
Looking to return "WWWW-FM"

In both situations, i can't use the straight LEFT Command because the list
has different number of characters. I might be able to easily replace the
second dash with a different common character, but this formula includes
the
common character and i would prefer not to see it.

Thanks - Any help is appreciated.
Rugdoody



"CLR" wrote:

Sorry, this should do it for you.........

=MID(A1,FIND("-",A1,1)+1,99)

Vaya con Dios,
Chuck, CABGx3



"luvthavodka" wrote:

Thanks for this, however it is returning -43GF, rather than 43GF.

How can I expand on this formula to also remove the "-"?

Many thanks

"CLR" wrote:

With your data in column A, put this in B1 ad copy down........

=MID(A1,FIND("-",A1,1),99)

Vaya con Dios,
Chuck, CABGx3



"luvthavodka" wrote:

I have a clomun of cells with various codes in e.g:

Column A
42NAFF16-43
927PLY833-16FF
16ARBY1-1

etc..

The only common factor in all these references is the dash symbol.
I am
looking to return in another column just the figures after the dash
e.g:

Column B
43
16FF
1

I'm stuck as to what formula to use as I'm only used to chopping
off a fixed
number of digits! Any help is greatly appreciated.