Removing the first space only

**emmo** · 03-23-2012, 10:18 AM

Hi,

I have an excel sheet where I need to remove the first space of the string ONLY. My information looks like this:

Column Column Column Column
Sean Test The Testing Company Director and CEO

There is a space between the cell wall and the beginning of the text string. I've used the trim function, however it removed all the spaces and made my sheet look like this:

Sean Test TheTestingCompany DirectorandCEO

Is there anyway just to remove the first space of the string?

thanks!

**Søren Larsen** · 03-23-2012, 10:27 AM

=mid(ref_to_text;2;256)

**emmo** · 03-23-2012, 10:33 AM

Great, That worked aweosme! Thank you ! Do you know how I can get my text that looks like TheTrainingCompany to look like The Training Company again? The original file was lost

**martindwilson** · 03-23-2012, 10:39 AM

trim doesn't remove all spaces ,how exactly are you using it? post a sample worksheet.
you can get rid of leading spaces in a column by using text to columns /fixed width/remove any break lines/click finish

**emmo** · 03-23-2012, 10:44 AM

Also a great suggestion that works

! thanks!

any advise for fixing the mistake?

I'm not sure how it was used to be honest, my colleague used it then sent the sheet to me to see if I could correct it

I guess she deleted the original which is why I don't know how to fix the TheTestingCompany since all the other merged strings are different lengths (ex. TheTestingCompany , ThreeTest ) so I can't use text to columns

**martindwilson** · 03-23-2012, 11:19 AM

that's going to be difficult! there are no functions that will do that in excel ,code splitting on caps can be done
but i find it easier just to start again
copy paste into word as unformatted text
then find replace make sure using wild cards option is selected
find
[A-Z]
replace with
^s^&
that will put your spaces back then copy back into excel

**Armando Montes** · 03-23-2012, 11:23 AM

Try this:
http://www.mrexcel.com/forum/showthr...56#post2095356

pgc01

Sub CamelCase()
Dim rC As Range

With CreateObject("VBScript.RegExp")
.Pattern = "([a-z])([A-Z])"
.Global = True
For Each rC In Selection
rC.Parse "[" & .Replace(rC, "$1][$2") & "]", rC.Offset(, 2)
Next rC
End With
End Sub