Re: Need some help with a text manipulation macro (longish)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

superdee_at_degs.co.uk
Date: 12/10/04 

Date: Fri, 10 Dec 2004 22:42:27 +0000

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

On Fri, 10 Dec 2004 16:20:17 -0500, "David F. Schrader"
<schrader@acns.fsu.edu> wrote:

>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
>
>*------
>

Relevant Pages

  • Re: Help with MID Function?
    ... just use the last formula I posted back in your first thread on this problem; ... Change all the A1 references to whatever cell you have your text in. ... I'm trying to capture / extract specific data within a string of text ... I need to capture a string of numeric characters plus only ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Concatenating and copying columns data
    ... I am a newbee to Oracle SQL programming. ... Column B will always contain numeric characters. ... ASCII and leave the numeric characters as it is. ... and how to convert it to a string and to concatenate ...
    (comp.databases.oracle.misc)
  • Help with MID Function?
    ... I'm trying to capture / extract specific data within a string of text ... I need to capture a string of numeric characters plus only ... So my cells ...
    (microsoft.public.excel.worksheet.functions)
  • Re: String Generation using Mask Parsing
    ... to get some hands-on practise! ... string generator, ... followed by a sequence of 10 numeric characters. ... ten alternating alphanumeric/ numeric characters. ...
    (comp.lang.c)