Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to put spaces in a string of data
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to put spaces in a string of data
the following formula should do it
=LEFT(A1,4)&","&MID(A1,5,3)&","&MID(A1,8,3)&","&MI D(A1,11,3)&","&MID(A1,14,4)&","&MID(A1,18,1)&","&R IGHT(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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to put spaces in a string of data
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. "dmoney" wrote: the following formula should do it =LEFT(A1,4)&","&MID(A1,5,3)&","&MID(A1,8,3)&","&MI D(A1,11,3)&","&MID(A1,14,4)&","&MID(A1,18,1)&","&R IGHT(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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to put spaces in a string of data
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 "aileen" wrote: 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. "dmoney" wrote: the following formula should do it =LEFT(A1,4)&","&MID(A1,5,3)&","&MID(A1,8,3)&","&MI D(A1,11,3)&","&MID(A1,14,4)&","&MID(A1,18,1)&","&R IGHT(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" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to put spaces in a string of data
On Wed, 29 Oct 2008 10:15:01 -0700, aileen
wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to put spaces in a string of data
Thanks for all the responses. Everyone is always so helpful on this site.
It is much appreciated. "Ron Rosenfeld" wrote: On Wed, 29 Oct 2008 10:15:01 -0700, aileen wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find String in another string - only between spaces | Excel Worksheet Functions | |||
Value between two spaces in a String | Excel Programming | |||
counting spaces in a string | Excel Programming | |||
counting spaces in a string | Excel Programming | |||
Count Spaces In A String | Excel Programming |