Extracting spaces from a string of numbers

[johnnyrad]

First off, awesome site, Im so glad I found it. I've already learned
alot just surfing around, reading other posts.


I had a situation today, and we handled it one way, but Im thinking
there's a better way.

I have a column, each cell has a 22 digit number (gl codes, accounting)
in it. The person insertered spaces after some logical breaks, but we
needed the spaces removed in order to use their file for uploading.

ie. they provided:

D001 210 0014 12345 666111

we needed:

D001210001412345666111


We did a text to columns to break it up, and then concantenate to bring
it back together. That worked fine, except for the 3rd segment, and
how it is 0014, the text to columns stripped off the 00 part, so then
we had to take the extra step to force the colums to be the correct
number of digits long.


My other attempt was to write a macro.

I went to the first cell, A1. Then i hit record, F2, arrowed over and
deleted the spaces, hit enter, then hit Stop. That was great, except
when i used the macro on the following lines, which had different
values, for example D020 etc, it replaced each line with a copy of the
first one I used to record the macro. Any feedback on why it didn't
just do the operation of arrowing over and deleting spaces, and instead
also replaced the data?

 

[Jane Graves]

There are a couple of ways to do what you were doing more
easily. One is to higlight the column in question, do an
edit/Find (Ctrl-F). Find " " (without the quotes), and
hit the Replace button. Leave the replace field blank and
hit Replace all.

Otherwise, you could insert the following formula in a new
cell: SUBSTITUTE (A1," ",""). Copy the formula down, then
copy the column, Edit/Paste Special/Values. Then you can
delete the old column.

Hope that helps for next time.

Jane
jane.graves @ infores.com

 

[Dave R.]

You can use SUBSTITUTE(A1," ","") which will remove all spaces.

When you're in Excel and starting to enter a formula, you'll see a little
box change to the left of the formula bar. Click on teh down arrow and go to
"more functions" then you can look at functions that deal with text,
numbers, etc. which would have showed you SUBSTITUTE.

 

[A.W.J. Ales]

Johnnyrad,
Assuming your string is in cell A1 use

=SUBSTITUTE(A1;" ";"")

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Gord Dibben]

Johnny

Select the cells.

Edit>Replace

What: <space> means just enter a space
With: <nothing> means enter nothing

Replace all.

A macro to do same thing......

Public Sub Strip_WhiteSpace()
Selection.Replace What:=" ", _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Gord Dibben Excel MVP

 

[johnnyrad]

Cool thanks everyone!