Removing specific words from cell

[jw01]

hi there

i have a range of 1000 rows where it states city and "sales rep"

i want in cell B1 to show remove "Sales Rep" from the cell. below are some examples of the data points:
Ottawa Sales Rep = "Ottawa"
Sacramento Sales Rep = "Sacramento"
Long Island Sales Rep = "Long Island"

can someone pls help me with a formula? as u can see there are instances where some cities have two words i.e. Long Island

thanks alot!

[XOR LX]

Hi,

=TRIM(SUBSTITUTE(A1,"Sales Rep",""))

Regards

[martindwilson]

cant you just find/replace sales rep with nothing?

[jw01]

cant you just find/replace sales rep with nothing?

hello martindwilson

I would but i will have to do this again each week so it would be nice to have a formula.

thanks for the help.

[jw01]

Hi,

=TRIM(SUBSTITUTE(A1,"Sales Rep",""))

Regards

hello

that works great thank you

one more question....is it possible to add a "wildcard" in that? for example, anything that contains "sales r*"??

there are a few examples where the data is "Miami Sales Rp" or "New Jersey Sales R"

thxs.

[XOR LX]

Unfortunately wildcards do not work within the SUBSTITUTE function.

Perhaps:

=TRIM(SUBSTITUTE(A1,MID(A1,FIND("Sales R",A1),FIND(" ",A1,FIND("Sales R",A1)+6)-FIND("Sales R",A1)),""))

Regards

[jw01]

hello there

i placed your formula:
=TRIM(SUBSTITUTE(C2,MID(C2,FIND("Sales R",C2),FIND(" ",C2,FIND("Sales R",C2)+6)-FIND("Sales R",C2)),""))

but getting #value error on the first entry.

the first entry is Hawaii Sales Rep

?

[XOR LX]

Apologies.

Try:

=TRIM(SUBSTITUTE(C2,MID(C2,FIND("Sales R",C2),FIND(" ",C2&" ",FIND("Sales R",C2)+6)-FIND("Sales R",C2)),""))

Regards

[martindwilson]

or
=IF(ISNUMBER(SEARCH("sales r",C2)),LEFT(C2,SEARCH("sales r",C2)-2)&MID(C2,SEARCH(" ",C2&" ",SEARCH("sales r",C2)+7),255),C2)

[purpleaddy]

Hello, I wonder if someone can help me to remove part of entries in cells leaving me with just names in order that I can sort by same. I have nearly 4000 entries and so could take me some time if I do them individually. This is what is in my cells:

36,"0847363341","SMITH,ALEX","SMIT02","","9"," ","1",50,40,60,0,0,0,0,0,0,"1",250000,0,1378290997,N,N,N,N,N,Y,Y,N,Y,N,N,1378290997,"","","",0

Here's hoping,

purpleaddy

[martindwilson]

purpleaddy welcom to the forum BUT
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[benishiryo]

hi jw01. if you just want everything before "sales r" like in all your egs, maybe this simple one will do:
=LEFT(C2,SEARCH("sales r",C2)-1)