Need some help with a text manipulation macro (longish)

[David F. Schrader]

Hi to those willing to brave the length...

I have some data that has been entered, a 16 digit field formatted,
something like a Social Security Number. (It is suppose to look
like this: 5894 3710 0276 1002.) Of course, during the data
entry phase no one has followed the guide lines and now I am
trying to find a(n easy) way to reformat the data which is stored
in a column of an Excel spreadsheet.

At first I thought that simply going a "Replace" *All* might solve
the problem (see what I've included below which I culled out of a
macro I "recorded"). But Then I discovered that no two data entry
personnel offices had entered the data in the same columns. *ARGG*
(And of course there were other problems as well.)

My question is:

Does some one have a simple and more or less "clean" suggestion
that could be used with the

For Each cell_in_loop In Range("A2:A2000")
If cell_in_loop.Value = "EOList" Then
Exit For
End If
{
For each cell_in_loop.Offset(0, 9).Value
Perform the process each cell in the
column converting it to meet the "specs"
}
Next

I started off with the idea of a "Mid" but the code got way to long
and way to convoluted - I couldn't even figure out what I was trying
to do when I came back after I when home for a nights rest.

All help welcomed and appreciated.

David Schrader


*------
A code snipit


Application.ScreenUpdating = False
Cells.Replace What:="5894 37100", _
Replacement:="5894 3710 0", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Application.ScreenUpdating = False
Cells.Replace What:="5894 37109...", _
Replacement:="5894 37109...", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
:
Trimmed
:
Cells.Replace What:="5894 3710 1", _
Replacement:="5894 3710 1", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
:
Trimmed
:
Cells.Replace What:="5894 3710 9", _
Replacement:="5894 3710 - 9", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Application.ScreenUpdating = False

*------

 

[Dave]

on a general look you may just be better off combining all the fields into
one long string, then stripping out anything that isn't a numeral, then
reformatting it into the pieces you want.

 

[superdee]

I have had a similar problem and I

removed all non numeric characters in the string, and at each end of
the string.

If you are left with a 16 digit field you can format it by putting
a'-' character in the 5th, 10th and 15th positions.

So if your data is in column A then A1 would contain say
23454325665781234

the formula in B1 would be

=CONCATENATE(LEFT(A1,4),"-",MID(A1,5,4),"-",MID(A1,9,4),"-",MID(A1,13,4))

giving

2345-4325-6657-8120

 

[David F. Schrader]

My thanks to (e-mail address removed) <[email protected]> and
Dave ([email protected]) for their suggestion.

Turned out that there was no "elegant" solution that also minimized
machine time. Easiest, cleanest way was to make a pass though
each character. If it was *in* [1..0} then keep else discard; then,
as suggested by "superdee," insert the appropriate spacing.

Many thanks.

David