Inserting space between number and letter

[mikezoro]

Hi again, guys!

I was looking at the old posts in the forum and I ran into this post:

http://www.mrexcel.com/forum/excel-...cations-put-space-between-string-numbers.html

My problem is almost the same, the problem is that I need to add a space in between a number and a letter and these macros seem to work the other way around (creating a space between a letter and a number).

Tried to change some things... still no results. I have a ~16,000 row file, and some cells have measurement units like:

2,200c.u. or 426cc

I'm looking for a way to add a space in between IF there's no space, there are some rows that are correctly formatted and I'd hate to have double spaces there. Any help is, as usual, very welcome.

Column to be formatted would be "H" (there are other columns with data that I'm not supposed to change). Using Excel 2010.

 

[patel45]

attach a link to a sample file with current and desired columns

 

[kpark91]

Are the values always like 2,200c.u and 426cc where the digits appear first and then the alphabets?

 

[mikezoro]

Hi guys...

Here's more info:

Columns A -F (Untouchable stuff like ID codes, etc.
Column G English version of the data (this is an XLSX translation file)
Column H Spanish version of the data

Some rows are just words like

[TABLE="width: 500"]
<tbody>[TR]
[TD]ENGLISH
[/TD]
[TD]SPANISH
[/TD]
[/TR]
[TR]
[TD]Model
[/TD]
[TD]Modelo
[/TD]
[/TR]
[TR]
[TD]Banner (2.8in.x8in.)
[/TD]
[TD]Banner (2.8pulg.x8pulg.)
[/TD]
[/TR]
[TR]
[TD]80mm
[/TD]
[TD]80mm
[/TD]
[/TR]
</tbody>[/TABLE]

In the last two rows, I'd need to add a space (2.8in >> 2.8 and 80mm >> 80 mm)

hope this info helps

 

[Rick Rothstein]

Give this macro a try...

Sub InsertSpaceBetweenDigitAndLetter()
  Dim X As Long, Z As Long, LastRow As Long, vArr As Variant
  Const DataColumn As String = "A"
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  vArr = Cells(StartRow, DataColumn).Resize(LastRow - StartRow + 1)
  For X = 1 To UBound(vArr)
    For Z = Len(vArr(X, 1)) - 1 To 1 Step -1
      If Mid(vArr(X, 1), Z, 1) Like "#" And Mid(vArr(X, 1), Z + 1, 1) Like "[A-Za-z]" Then
        vArr(X, 1) = Left(vArr(X, 1), Z) & " " & Mid(vArr(X, 1), Z + 1)
      End If
    Next
  Next
  Cells(StartRow, DataColumn).Resize(LastRow - StartRow + 1) = vArr
End Sub

You can adjust the constants (Const statements) to match your actual setup.

 

[mikezoro]

Hi Ric...

Your macro does the trick... however, it changed the cells with numbers to a different format...

Ex. 1/1 changed to Jan-01... and 23578 (left aligned) changed to 23578 (right aligned).

other than that, it did the trick... thanks a lot!

 

[Rick Rothstein]

Hi Ric...

Your macro does the trick... however, it changed the cells with numbers to a different format...

Ex. 1/1 changed to Jan-01... and 23578 (left aligned) changed to 23578 (right aligned).

other than that, it did the trick... thanks a lot!

I am not sure what you are referring to here... can you show us sample original data that is getting changed the way you indicate above?

 

[mikezoro]

[TABLE="width: 500"]
<tbody>[TR]
[TD]English
[/TD]
[TD]Spanish
[/TD]
[/TR]
[TR]
[TD]Flag 15m
[/TD]
[TD]Bandera 15 m
[/TD]
[/TR]
[TR]
[TD]1/1
[/TD]
[TD="align: right"]Jan-01
[/TD]
[/TR]
[TR]
[TD]512478
[/TD]
[TD="align: right"]512478
[/TD]
[/TR]
[TR]
[TD]24/2
[/TD]
[TD="align: right"]Feb-24
[/TD]
[/TR]
</tbody>[/TABLE]

Anyway.. I must say that there were just a few... and I was able to put them back to "text" status... it took me way less time than looking and correcting all of the measurement units... so, that last macro was a huge success... thanks a lot!

 

[Rick Rothstein]

[TABLE="width: 500"]
<TBODY>[TR]
[TD]English
[/TD]
[TD]Spanish
[/TD]
[/TR]
[TR]
[TD]Flag 15m
[/TD]
[TD]Bandera 15 m
[/TD]
[/TR]
[TR]
[TD]1/1
[/TD]
[TD="align: right"]Jan-01
[/TD]
[/TR]
[TR]
[TD]512478
[/TD]
[TD="align: right"]512478
[/TD]
[/TR]
[TR]
[TD]24/2
[/TD]
[TD="align: right"]Feb-24
[/TD]
[/TR]
</TBODY>[/TABLE]

Anyway.. I must say that there were just a few... and I was able to put them back to "text" status... it took me way less time than looking and correcting all of the measurement units... so, that last macro was a huge success... thanks a lot!

For future use, I think this macro will work the way you actually want...

Sub InsertSpaceBetweenDigitAndLetter()
  Dim X As Long, Z As Long, LastRow As Long, vArr As Variant
  Const DataColumn As String = "A"
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  vArr = Cells(StartRow, DataColumn).Resize(LastRow - StartRow + 1).Value
  For X = 1 To UBound(vArr)
    For Z = Len(vArr(X, 1)) - 1 To 1 Step -1
      If Mid(vArr(X, 1), Z, 1) Like "#" And Mid(vArr(X, 1), Z + 1, 1) Like "[A-Za-z]" Then
        vArr(X, 1) = Left(vArr(X, 1), Z) & " " & Mid(vArr(X, 1), Z + 1)
      End If
    Next
  Next
  Cells(StartRow, DataColumn).Resize(LastRow - StartRow + 1).NumberFormat = "@"
  Cells(StartRow, DataColumn).Resize(LastRow - StartRow + 1) = vArr
End Sub