# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Remove leading space

## Karidre

I wonder if someone can help me with this...it's driving me crazy.

I need to delete a leading space from multiple cells, 200 or more. The
leading space is in front of text that is often more than one word so I can't
just copy into Word and do a find and replace on the spaces.

I looked in many places for help and found out about the Trim function but
when I tried it, it didn't work for me. Am I doing it wrong? I created a
column next to the column that I want to remove the leading spaces from. then
I put =TRIM(B2) in the first cell, =TRIM (B3) in the next one, etc. But all
it did was put the exact same thing as before (with the leading space still
in it) in that column?

Any suggestions? Thanks!

- Kari

----------


## JMB

The trim function should have worked if it was just a space entered w/the
spacebar.

It could be a special character that appears as a space.  Try
Search/Replace.  In the search line, hold down the Alt key and hit 255 on the
number keypad.  Then hit Replace All.

Be sure to back up your workbook.




"Karidre" wrote:

> I wonder if someone can help me with this...it's driving me crazy.
>
> I need to delete a leading space from multiple cells, 200 or more. The
> leading space is in front of text that is often more than one word so I can't
> just copy into Word and do a find and replace on the spaces.
>
> I looked in many places for help and found out about the Trim function but
> when I tried it, it didn't work for me. Am I doing it wrong? I created a
> column next to the column that I want to remove the leading spaces from. then
> I put =TRIM(B2) in the first cell, =TRIM (B3) in the next one, etc. But all
> it did was put the exact same thing as before (with the leading space still
> in it) in that column?
>
> Any suggestions? Thanks!
>
> - Kari

----------


## Biff

Hi!

Maybe those leading spaces aren't your standard leading spaces, char 32!
They may be some other unseen character like the dreaded char 160.

Try finding out what the actual characters are. Use this formula in a
temporary helper column:

=CODE(B2)

Once you know what those characters are then they should be fairly easy to
get rid of using either Edit>Replace or, maybe a formula like:

=MID(B2,2,255)

Or:

=SUBSTITUTE(B2,LEFT(B2),"")

Then you could convert the formulas to constants by doing a Copy>Paste
Special>Values. Then deleting the original data.

Biff

"Karidre" <Karidre@discussions.microsoft.com> wrote in message
news:53795615-1056-4B08-940F-69DB84009BEA@microsoft.com...
>I wonder if someone can help me with this...it's driving me crazy.
>
> I need to delete a leading space from multiple cells, 200 or more. The
> leading space is in front of text that is often more than one word so I
> can't
> just copy into Word and do a find and replace on the spaces.
>
> I looked in many places for help and found out about the Trim function but
> when I tried it, it didn't work for me. Am I doing it wrong? I created a
> column next to the column that I want to remove the leading spaces from.
> then
> I put =TRIM(B2) in the first cell, =TRIM (B3) in the next one, etc. But
> all
> it did was put the exact same thing as before (with the leading space
> still
> in it) in that column?
>
> Any suggestions? Thanks!
>
> - Kari

----------


## Paul Skinner

On 24/10/05 2:03 PM, in article
53795615-1056-4B08-940F-69DB84009BEA@microsoft.com, "Karidre"
<Karidre@discussions.microsoft.com> wrote:

> I wonder if someone can help me with this...it's driving me crazy.
>
> I need to delete a leading space from multiple cells, 200 or more. The
> leading space is in front of text that is often more than one word so I can't
> just copy into Word and do a find and replace on the spaces.
>
> I looked in many places for help and found out about the Trim function but
> when I tried it, it didn't work for me. Am I doing it wrong? I created a
> column next to the column that I want to remove the leading spaces from. then
> I put =TRIM(B2) in the first cell, =TRIM (B3) in the next one, etc. But all
> it did was put the exact same thing as before (with the leading space still
> in it) in that column?
>
> Any suggestions? Thanks!
>
> - Kari
Try :- Insert an empty column on the right of your Data, select the column
you want to fix. Go to top menu- Data, Text to column, This will show you a
preview of the text You wish to fix. Click next, Click the checkbox space,
Click next, click finish. Done. Then You can copy data back to where you
want it.
Paul

----------


## Dave Peterson

If you're getting this data from a web page, you may have non-breaking spaces
(HTML stuff) in those cells.

You may want to try David McRitchie's routine to clean that stuff up:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=========
If you want to try a formula, this may work:

=trim(substitute(a1,char(160)," "))

It changes the non-Breaking character to a space, then trims the value.

Excel also has =clean() that you may want to review, too.

Karidre wrote:
>
> I wonder if someone can help me with this...it's driving me crazy.
>
> I need to delete a leading space from multiple cells, 200 or more. The
> leading space is in front of text that is often more than one word so I can't
> just copy into Word and do a find and replace on the spaces.
>
> I looked in many places for help and found out about the Trim function but
> when I tried it, it didn't work for me. Am I doing it wrong? I created a
> column next to the column that I want to remove the leading spaces from. then
> I put =TRIM(B2) in the first cell, =TRIM (B3) in the next one, etc. But all
> it did was put the exact same thing as before (with the leading space still
> in it) in that column?
>
> Any suggestions? Thanks!
>
> - Kari

--

Dave Peterson

----------


## punkghetto

> Hi!
> 
> Maybe those leading spaces aren't your standard leading spaces, char 32!
> They may be some other unseen character like the dreaded char 160.
> 
> Try finding out what the actual characters are. Use this formula in a
> temporary helper column:
> 
> =CODE(B2)



Thanks Biff, I was trying to use the clean function for a similar problem, but couldn't get a result until I used the CODE() function to find out which character type was used in my data. Much appreciated

----------


## anil.sapre

Thanks Dave 

I've the same problem with french phone number format i.e. "_04 94 96 49 05". when I use this formula    =trim(substitute(a1,char(160)," ")).
It's amazing !
It works... problem solved. :Smilie: 

thanks again, Dave.

----------


## otellabgaveldot

Another contribution from an Excel bodger.

Save Your Worksheet  in case of problems.
Then Select one cell with the leading Space. 
Go to the Formula Bar at the top of the worksheet. 
Select and copy the leading space showing there by holding down the left mouse button and using Ctrl C.  
Then select all the cells with the leading space.
Use Ctrl F/Replace and Ctrl V to paste in the leading character (it probably won't be visible in the search field) to search for and replace the leading space in the selected cells. 
Just Use 'Replace' until you are sure that it works OK then Use 'Replace  All'.

Check the formatting of the altered cells to suit your intentions.

Thanks for all the other useful stuff that others proposed

----------


## tgallag1

> Another contribution from an Excel bodger.
> 
> Save Your Worksheet  in case of problems.
> Then Select one cell with the leading Space. 
> Go to the Formula Bar at the top of the worksheet. 
> Select and copy the leading space showing there by holding down the left mouse button and using Ctrl C.  
> Then select all the cells with the leading space.
> Use Ctrl F/Replace and Ctrl V to paste in the leading character (it probably won't be visible in the search field) to search for and replace the leading space in the selected cells. 
> Just Use 'Replace' until you are sure that it works OK then Use 'Replace  All'.
> ...



I had the same problem and came across this solution.  It was very helpful.  Thank you  :Smilie:

----------


## otellabgaveldot

tgallag1
Glad to help.

otellabgaveldot

----------


## rajani.indegene

hay man try this 

=TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " ")))

----------


## kisanvikas2015

Hi all,

Can someone please help me how can I use formula in data validation so that user can not enter leading space in front of the text.
I need a error alert pop up kind of notification & thus I need help.

Regards,
Vikas

----------


## protonLeah

kisanvikas2015,
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.
When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

_(link above in the menu bar)_

----------

