Removing Front Space

[Oithematt]

I deal a lot with copy and paste data. Sometimes there is a front space which messes up the sorting function.

Is there a way to remove front space from multiple cells at one time? Or is the answer going one by one and removing the spaces individually?


Thanks in advance

[Pete_UK]

Are you sure it is a "normal" space (with a code of 32), or could it be a non-breaking space (code = 160) ?

You could try the TRIM function in a separate column, which will remove leading and trailing spaces, as well as multiple spaces, but it does only act on normal spaces.

If you are certain it is only a single "space", then you could use the RIGHT function in conjunction with LEN.

You could also use the SUBSTITUTE function to change CHAR(160) characters to nothing.

But, it would help to see what your data looks like, so could you attach a sample workbook?

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

[Sam Capricci]

it depends, if it is a leading space and there are no other spaces then find and replace all should work.
a different option is to use trim which will remove leading and trailing spaces.

[Oithematt]

The current selection I am dealing with I had to use Find & Select > Go To Special > Objects and deleted images which left a space that Trim does not seem to affect

[Oithematt]

Workbook added.

As you can see there is a front space starting with Amanda Palmer through Zolar X then it goes back to my normal list alphabetized correctly

[Oithematt]

Unless i am using Trim incorrectly, which I probably am.

[Oithematt]

The current selection I am dealing with I had to use Find & Select > Go To Special > Objects and deleted images which left a space that Trim does not seem to affect

Workbook added.

As you can see there is a front space starting with Amanda Palmer through Zolar X then it goes back to my normal list alphabetized correctly

[Pete_UK]

If you put this formula in C6:

=CODE(A6)

it returns 160. You can get rid of all those non-breaking spaces (TRIM does not work on them) in one operation by highlighting column A and using Find & Replace (CTRL-H), with:

Find What: Alt-0160

Replace with: (leave blank)

Click Replace All, where Alt-0160 means to hold down the Alt key and type 0160 on the numeric keypad (or you could just copy that character from one of the offending cells).

When I did it on your file, there were 6436 replacements.

Hope this helps.

Pete

[Sam Capricci]

Ok, what I did was go to the first cell with the space (it may not be a typical space) and highlighted the space preceding Amanda's name and copied it, then pasted it into find and replace, left replace blank and hit replace all and they are all gone BUT not the ones between first and last names. try that, you can always hit undo if you don't like the results.

[Oithematt]

Thank you all so much, both suggestions worked amazingly.

[Pete_UK]

Glad to help, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Pete