Insert space 10 chars from end of row within indiv cells containing many rows

Jaffey · Jul 27th 2015

I have a spreadsheet with about 10,000 rows where each cell is populated with data similar to the following:

0GoodwinWayne91 National CresBramptonONL7A 1G9
SLNmoved: GoodwinWPoplar DrBewdleyONK0L 1E09057972520
SLNmoved: GoodwinWilliam HFrench RiverDokisON 7057632387
Ph# add : BarnesR91 National CresBramptonONL7A 1G99058748199
Neigh : HalsteadN88 National CresBramptonONL7A 1G99059709708
Neigh : IrelandAndy93 National CresBramptonONL7A 1G99058402497
Neigh : ParasilitiG89 National CresBramptonONL7A 1G99058462622
Neigh : ShakyaT88 National CresBramptonONL7A 1G99059153080
SLN : GoodwinB75 Charolais BlvdBramptonONL6Y 2R89054500912
SLN : GoodwinChristopher28 Lsburgh DrBramptonONL6X 4Z49054529993
SLN : GoodwinG103 Manitou CresBramptonONL6S 2Z69052301779

forum.ozgrid.com/index.php?attachment/66333/

The challenge is to insert a space 10 characters from the end of each row - starting with the 2nd row -within the cell so that the phone# is separated from the postal code.
There is also a carriage return/line feed character at the end of each row. I have attached some actual sample data as well. Many thanks!!

This is what the data should look like
0GoodwinWayne91 National CresBramptonONL7A 1G9
SLNmoved: GoodwinWPoplar DrBewdleyONK0L 1E0 9057972520
SLNmoved: GoodwinWilliam HFrench RiverDokisON 7057632387
Ph# add : BarnesR91 National CresBramptonONL7A 1G9 9058748199
Neigh : HalsteadN88 National CresBramptonONL7A 1G9 9059709708
Neigh : IrelandAndy93 National CresBramptonONL7A 1G9 9058402497
Neigh : ParasilitiG89 National CresBramptonONL7A 1G9 9058462622
Neigh : ShakyaT88 National CresBramptonONL7A 1G9 9059153080
SLN : GoodwinB75 Charolais BlvdBramptonONL6Y 2R8 9054500912
SLN : GoodwinChristopher28 Lsburgh DrBramptonONL6X 4Z4 9054529993
SLN : GoodwinG103 Manitou CresBramptonONL6S 2Z6 9052301779

**Carim** · Jul 28th 2015

Re: Insert space 10 chars from end of row within indiv cells containing many rows

Hi Jaffey,

Keeping all the rows in the same cell can make your life more complicated ...

Should you split in individual rows ... you could test following formula :

=LEFT(A1,LEN(A1)-12)&" Ph: "&RIGHT(A1,12)

HTH

jindon · Jul 28th 2015

Re: Insert space 10 chars from end of row within indiv cells containing many rows

This should do

Code

Sub test()
    Dim a, i As Long
    With Cells(1).CurrentRegion
        a = .Value
        With CreateObject("VBScript.RegExp")
            .Global = True: .MultiLine = True
            .Pattern = "(\d{10} *)$"
            For i = 1 To UBound(a, 1)
                a(i, 1) = .Replace(a(i, 1), " $1")
            Next
        End With
        .Value = a
    End With
End Sub

Display More

Jaffey · Aug 5th 2015

Re: Insert space 10 chars from end of row within indiv cells containing many rows

Thank you Jindon!! It worked perfectly. The actual data I needed to process was in column 37 so I changed:

a(i, 1) = .Replace(a(i, 1), " $1")

to

a(i, 37) = .Replace(a(i, 37), " $1")

Please let me know if there is anything else I should modify. Thank you very much.

jindon · Aug 5th 2015

Re: Insert space 10 chars from end of row within indiv cells containing many rows

Just try change

Code

With Cells(1).CurrentRegion

to

Code

With Cells(1).CurrentRegion.Columns(37)

So that it only read/write one column, otherwise it overwrites whole data.

Insert space 10 chars from end of row within indiv cells containing many rows

Participate now!

Share