Trim, Mid...?

orbea_adam

Well-known Member
Joined
Nov 15, 2005
Messages
500
Hi Guys, I have data formatted as "General" that looks like the following:

5144 / 22 - Both - LWRV

510 / 8 - Both - DO

5377 / 4 - Annual - LWRV

101 / 25 - Both - DO

How should I get rid of the spaces and keep just the numbers and " / "? I can think of trim, mid, left, right, but the number of characters will always vary as in the example.
Thanks
Adam
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Here's one way, assuming that there is a dash as shown:

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

It gets rid of all spaces, finds the dash and uses that in the left function.
 

orbea_adam

Well-known Member
Joined
Nov 15, 2005
Messages
500
Here's one way, assuming that there is a dash as shown:

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

It gets rid of all spaces, finds the dash and uses that in the left function.
Thank you
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I imagine you only need one SUBSTITUTE....

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

orbea_adam

Well-known Member
Joined
Nov 15, 2005
Messages
500
I imagine you only need one SUBSTITUTE....

=SUBSTITUTE(LEFT(A1,FIND("-",A1&"-")-1)," ","")
Barry,
Thank you for the solution, but can you explain how it works, please?
Adam
 

Watch MrExcel Video

Forum statistics

Threads
1,113,792
Messages
5,544,311
Members
410,602
Latest member
lidovi
Top