Is it possible to put spaces in a string of data

[aileen]

I have two columns with strings of data as such:

Column A Column B

2008SPX850OCT0P2103 2008SPX850OCT0P2303
2008SPX855DEC0P208 2008SPX855DEC0P120
2008SPX855DEC-208C0 2008SPX855DEC-120C0
2008SPX1020DEC0P1022 2008SPX1020DEC0P1038

Is it possible to insert a space or comma between each number and text? I
need to keep the minus sign(-) with the number that comes after it.
e.g.
Column A Column B

2008,SPX,850,OCT,0,P,2103 2008,SPX,850,OCT,0,P,2303
2008,SPX,855,DEC,0,P,208 2008,SPX,855,DEC,0,P,120
2008,SPX,855,DEC,-208,C,0 2008,SPX,855,DEC,-120,C,0
2008,SPX,1020,DEC,0,P,1022 2008,SPX,1020,DEC,0,P,1038

Thanks for any help.

 

[dmoney]

the following formula should do it

=LEFT(A1,4)&","&MID(A1,5,3)&","&MID(A1,8,3)&","&MID(A1,11,3)&","&MID(A1,14,4)&","&MID(A1,18,1)&","&RIGHT(A1,1)

you may have to adjust for areas where the string lengths are different

the formula above worked for this line 2008SPX855DEC-208C0

 

[aileen]

Thanks for the quick response. My data changes all the time so I used your
idea to add the commas when I create the string instead of after it's been
created. It's now working the way I need. Thanks for the help.

 

[Mike H]

Hi,

A bit messy but this should work for any string, Assumes data in colukn A

Sub BrainAche()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A1:A" & lastrow)
For Each c In myrange
oldstring = c.Text
For x = 1 To Len(oldstring)
p = Asc(Mid(oldstring, x + 1, 1))
If IsNumeric(Mid(oldstring, x, 1)) And p > 57 Then
oldstring = Left(oldstring, x) & "," & Mid(oldstring, x + 1)
End If
Next
For x = 1 To Len(oldstring)
p = Asc(Mid(oldstring, x + 1, 1))
If p > 47 And p < 58 And Asc(Mid(oldstring, x, 1)) > 57 Then
oldstring = Left(oldstring, x) & "," & Mid(oldstring, x + 1)
End If
Next
If InStr(oldstring, "-") <> 0 Then
oldstring = Left(oldstring, InStr(oldstring, "-") - 1) & "," &
Mid(oldstring, InStr(oldstring, "-"))
End If
c.Offset(, 1).Value = oldstring
Next
End Sub

Mike

 

[Ron Rosenfeld]

I have two columns with strings of data as such:

Column A Column B

2008SPX850OCT0P2103 2008SPX850OCT0P2303
2008SPX855DEC0P208 2008SPX855DEC0P120
2008SPX855DEC-208C0 2008SPX855DEC-120C0
2008SPX1020DEC0P1022 2008SPX1020DEC0P1038

Is it possible to insert a space or comma between each number and text? I
need to keep the minus sign(-) with the number that comes after it.
e.g.
Column A Column B

2008,SPX,850,OCT,0,P,2103 2008,SPX,850,OCT,0,P,2303
2008,SPX,855,DEC,0,P,208 2008,SPX,855,DEC,0,P,120
2008,SPX,855,DEC,-208,C,0 2008,SPX,855,DEC,-120,C,0
2008,SPX,1020,DEC,0,P,1022 2008,SPX,1020,DEC,0,P,1038

Thanks for any help.

Since you posted in the Programming book, here is a macro that will accomplish
what you describe. It assumes that the strings consist only of digits, letters
or the minus sign.

As written, it will insert commas into the cells that you have SELECTED. It
will also test the range to ensure that there are no commas there already.

=============================================
Option Explicit

Sub AddCommas()
Dim c As Range
Dim sTemp As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "([\-0-9]+|[A-Za-z]+)"
For Each c In Selection
sTemp = c.Value
If InStr(1, sTemp, ",") = 0 And _
re.test(sTemp) = True Then
sTemp = re.Replace(sTemp, "$1,")
'to add <space> instead of <comma>, change
' "$1," to "$1 "
c.Value = Left(sTemp, Len(sTemp) - 1)
End If
Next c
End Sub
==============================================
--ron

 

[aileen]

Thanks for all the responses. Everyone is always so helpful on this site.
It is much appreciated.

I have two columns with strings of data as such:

Column A Column B

2008SPX850OCT0P2103 2008SPX850OCT0P2303
2008SPX855DEC0P208 2008SPX855DEC0P120
2008SPX855DEC-208C0 2008SPX855DEC-120C0
2008SPX1020DEC0P1022 2008SPX1020DEC0P1038

Is it possible to insert a space or comma between each number and text? I
need to keep the minus sign(-) with the number that comes after it.
e.g.
Column A Column B

2008,SPX,850,OCT,0,P,2103 2008,SPX,850,OCT,0,P,2303
2008,SPX,855,DEC,0,P,208 2008,SPX,855,DEC,0,P,120
2008,SPX,855,DEC,-208,C,0 2008,SPX,855,DEC,-120,C,0
2008,SPX,1020,DEC,0,P,1022 2008,SPX,1020,DEC,0,P,1038

Thanks for any help.

Since you posted in the Programming book, here is a macro that will accomplish
what you describe. It assumes that the strings consist only of digits, letters
or the minus sign.

As written, it will insert commas into the cells that you have SELECTED. It
will also test the range to ensure that there are no commas there already.

=============================================
Option Explicit

Sub AddCommas()
Dim c As Range
Dim sTemp As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "([\-0-9]+|[A-Za-z]+)"
For Each c In Selection
sTemp = c.Value
If InStr(1, sTemp, ",") = 0 And _
re.test(sTemp) = True Then
sTemp = re.Replace(sTemp, "$1,")
'to add <space> instead of <comma>, change
' "$1," to "$1 "
c.Value = Left(sTemp, Len(sTemp) - 1)
End If
Next c
End Sub
==============================================
--ron