MS Excel Formulas

dickie999

dickie999

dickie999

Associate
Joined
5 Mar 2012
Posts
128
Location
South Shields
Hey Guys,

Looking to see if anyone can help me with a formula I am struggling with.

In my excel sheet I am trying to change "Full Name" in A1 ie. "John Smith" to "JohnS" in b1.

Using =LEFT(A1,FIND(" ",A1,1)+1) in B1 gives the result "John S"

But for the life of my I can not remove the space, I have tried using SUBSTITUTE(" ","") along with TRIM but formula is just failing where ever i try to add it.

I current have this ATM which is not working..

=LEFT(A2,FIND(" ",A2,1)+1,substitute(" ",""))

Any help would be greatly appreciated

Dickie
 
As above substitute function will do it, alternatively you could just us a concatenate function ('&') to tag onto something similar to what you have already and pull in the character after the space.

=LEFT(A1,(FIND(" ",A1)-1))&MID(A1,(FIND(" ",A1))+1,1)
 
The quickest way to do this would be with Flash Fill. Type in a couple of examples of what you need, then press Ctrl+E to do the "Flash Fill". Excel figures out what you're doing and applies it to the rest of the list. Like this:

Couple of examples of what you need:
vXrKGin.png


Press Ctrl+E and this happens:
KIA27kj.png
 
Holy biscuits that is an epic tip Grrr- never knew that!

Once it does its thing is there a way to expose the formula (it used) to achieve it?
 
You must log in or register to reply here.
Back
Top Bottom