Removing text from cells

[Craig]

Hi,

I have a spreadsheet with a list of reference numbers in
column A. The numbers are like this.

AAA/12345/Smith J Mr

What I want to do is remove the text after the last / eg.
Smith J Mr.

I have thousands of cells I need to remove this on.

Please help.

Thanks.

 

[JulieD]

Hi Craig

are all the entries the same number of characters for the first two
sections,
AAA/12345/
BBB/67890/
etc

if so you can do this using a "helper" column - in column B type
==LEFT(A1,10)
and then double click on the fill handle (bottom right hand side of cell) to
fill down
now copy column B
click on A1 and choose Edit / Paste Special Values
and then you can delete column B

(however, before trying this please take a backup of your workbook).

please let us know how you go

cheers
JulieD

 

[Frank Kabel]

Hi
you may try the following formula for this:
=LEFT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN
(A1)-LEN(SUBSTITUTE(A1,"/","")))))

 

[Craig]

Thanks Frank,

Your formula has partialy worked but what did'nt mention
in my fist post and probably should have is the all the
references are not like the examle. Here are some
references exactly as I have them.

IEMK/CLG/108226/Grounds Cliffo
JC/cnm/101272/StockHMr
GJH/KHF/SB/78366
RJ/120849
FB/KM/42746/Croxall

Yor formula only shows so many digits eg. IEMK/CLG/10822
appeared for the top reference of the list above.

Any Ideas?

 

[Frank Kabel]

Hi
sorry, made a mistake. Try
=LEFT(A1,SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN
(SUBSTITUTE(A1,"/","")))))

this returns
IEMK/CLG/108226/

for your first example

 

[Craig]

Give yourself a pat on the back Frank.

Thanks so much for your help.

 

[Harlan Grove]

sorry, made a mistake. Try
=LEFT(A1,SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)
-LEN(SUBSTITUTE(A1,"/","")))))

this returns
IEMK/CLG/108226/

for your first example

Yes, but your formula above returns

GJH/KHF/SB/
RJ/

for the 3rd and 4th sample lines. Maybe that's what the OP wants. If not,
and if those records should flow through as-is (as well as records
containing no slashes at all), then there's the array formula

=IF(OR(ISNUMBER(-RIGHT(A1,1)),COUNTIF(A1,"*/*")=0),A1,
LEFT(A1,MATCH(2,1/(MID(A1,ROW(INDIRECT("1:1024")),1)="/"))))