Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dividing an replacing spaces in string
You guys always steer me right.
How can I do this? I am showing spaces as ~ Everything is in a single cell. I don't need to do anything with the small groups of spaces 1-3 are ok. I need a Line break replacing the larger qtys. However they always vary in length. 2.31~QN~C/AS4~GAW~~~~~~~~~1.03~FW~YELLOW~~J/VVS13~~NEA~~~~~~~~~~~~40.04~HP~F/VVS2~~~MHA ~~~~~~~~~~~~~~~~~~111.51~AD~D/SI~ EMO~~~~~~~~~~~1.20~MX~L/TS~ GHL~ So generally I need, when there is a large span of spaces to convert each group to a vbLf. This newly delimited string will dump into separate rows like this 2.31 QN C/AS4 GAW 1.03 FW J/VVS13 NEA 40.04 HP F/VVS2 MHA 111.51 AD D/SI EMO 1.20 MX L/TS GHL Any ideas? Thanks as always scott |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dividing an replacing spaces in string
On Thu, 25 Jan 2007 02:47:37 -0800, scott d <please no email wrote:
You guys always steer me right. How can I do this? I am showing spaces as ~ Everything is in a single cell. I don't need to do anything with the small groups of spaces 1-3 are ok. I need a Line break replacing the larger qtys. However they always vary in length. 2.31~QN~C/AS4~GAW~~~~~~~~~1.03~FW~YELLOW~~J/VVS13~~NEA~~~~~~~~~~~~40.04~HP~F/VVS2~~~MHA ~~~~~~~~~~~~~~~~~~111.51~AD~D/SI~ EMO~~~~~~~~~~~1.20~MX~L/TS~ GHL~ So generally I need, when there is a large span of spaces to convert each group to a vbLf. This newly delimited string will dump into separate rows like this 2.31 QN C/AS4 GAW 1.03 FW J/VVS13 NEA 40.04 HP F/VVS2 MHA 111.51 AD D/SI EMO 1.20 MX L/TS GHL Any ideas? Thanks as always scott This should get you started. It parses the string in A1 into separate strings using Regular Expressions. It allows 1-3 sequential spaces, but anything more than that gets "split". This behavior is controlled by the Regular Expression in the constant Pattern. As written, it prints the string in [A1] split up into the immediate window, but should be easily modifiable to meet your specifications. ================================================== === Option Explicit Sub SplitLongSpaces() Dim objRe As Object Dim colMatches As Object Const Pattern As String = "(\S+\s{1,3})+" Dim str As String Dim i As Long Set objRe = CreateObject("vbscript.regexp") objRe.Global = True objRe.Pattern = Pattern str = [a1].Text & " " If objRe.test(str) = True Then Set colMatches = objRe.Execute(str) For i = 0 To colMatches.Count - 1 Debug.Print Trim(colMatches(i)) Next i End If End Sub ======================================== --ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dividing an replacing spaces in string
Change SPACECHAR to whatever your string is actually spaced with. The
control for the LF is set to at least 5 spaces. If you want to make this less or more change the line. Public Sub test() ScottD Range("A1").Value, Range("B3").Address End Sub Public Sub ScottD(strIn As String, startRange As String) ' change spacechar to whjatever your spaces are Const SPACECHAR = "~" Dim wb As Workbook Dim ws As Worksheet Dim r As Range Dim i As Integer, t As Integer, seq As Integer Set wb = ActiveWorkbook Set ws = wb.ActiveSheet Set r = ws.Range(startRange) For i = 1 To Len(strIn) If Mid(strIn, i, 1) = "~" Then seq = i Do While Mid(strIn, i, 1) = SPACECHAR i = i + 1 Loop seq = i - seq i = i - 1 ' this is the number of spacces required to LF If seq < 5 Then Set r = r.Offset(0, 1) Else Set r = ws.Cells(r.Row + 1, ws.Range(startRange).Column) End If Else t = InStr(i, strIn, "~") r.Value = Mid(strIn, i, t - i) i = t - 1 End If Next i Set r = Nothing Set ws = Nothing Set wb = Nothing End Sub Cheers, Jason Lepack On Jan 25, 5:47 am, scott d <please no email wrote: You guys always steer me right. How can I do this? I am showing spaces as ~ Everything is in a single cell. I don't need to do anything with the small groups of spaces 1-3 are ok. I need a Line break replacing the larger qtys. However they always vary in length. 2.31~QN~C/AS4~GAW~~~~~~~~~1.03~FW~YELLOW~~J/VVS13~~NEA~~~~~~~~~~~~40.04~HP~*F/VVS2~~~MHA ~~~~~~~~~~~~~~~~~~111.51~AD~D/SI~ EMO~~~~~~~~~~~1.20~MX~L/TS~ GHL~ So generally I need, when there is a large span of spaces to convert each group to a vbLf. This newly delimited string will dump into separate rows like this 2.31 QN C/AS4 GAW 1.03 FW J/VVS13 NEA 40.04 HP F/VVS2 MHA 111.51 AD D/SI EMO 1.20 MX L/TS GHL Any ideas? Thanks as always scott |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dividing an replacing spaces in string
Also, what happened to Yellow?
On Jan 25, 5:47 am, scott d <please no email wrote: You guys always steer me right. How can I do this? I am showing spaces as ~ Everything is in a single cell. I don't need to do anything with the small groups of spaces 1-3 are ok. I need a Line break replacing the larger qtys. However they always vary in length. 2.31~QN~C/AS4~GAW~~~~~~~~~1.03~FW~YELLOW~~J/VVS13~~NEA~~~~~~~~~~~~40.04~HP~*F/VVS2~~~MHA ~~~~~~~~~~~~~~~~~~111.51~AD~D/SI~ EMO~~~~~~~~~~~1.20~MX~L/TS~ GHL~ So generally I need, when there is a large span of spaces to convert each group to a vbLf. This newly delimited string will dump into separate rows like this 2.31 QN C/AS4 GAW 1.03 FW J/VVS13 NEA 40.04 HP F/VVS2 MHA 111.51 AD D/SI EMO 1.20 MX L/TS GHL Any ideas? Thanks as always scott |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
dividing an replacing spaces in string
ron,
I knew regular expressions would help in this. This was exactly what I needed. I have this run through almost 30 columns of data and it is perfect. thanks so much scott wrote: On Thu, 25 Jan 2007 02:47:37 -0800, scott d <please no email wrote: You guys always steer me right. How can I do this? I am showing spaces as ~ Everything is in a single cell. I don't need to do anything with the small groups of spaces 1-3 are ok. I need a Line break replacing the larger qtys. However they always vary in length. 2.31~QN~C/AS4~GAW~~~~~~~~~1.03~FW~YELLOW~~J/VVS13~~NEA~~~~~~~~~~~~40.04~HP~F/VVS2~~~MHA ~~~~~~~~~~~~~~~~~~111.51~AD~D/SI~ EMO~~~~~~~~~~~1.20~MX~L/TS~ GHL~ So generally I need, when there is a large span of spaces to convert each group to a vbLf. This newly delimited string will dump into separate rows like this 2.31 QN C/AS4 GAW 1.03 FW J/VVS13 NEA 40.04 HP F/VVS2 MHA 111.51 AD D/SI EMO 1.20 MX L/TS GHL Any ideas? Thanks as always scott This should get you started. It parses the string in A1 into separate strings using Regular Expressions. It allows 1-3 sequential spaces, but anything more than that gets "split". This behavior is controlled by the Regular Expression in the constant Pattern. As written, it prints the string in [A1] split up into the immediate window, but should be easily modifiable to meet your specifications. ================================================= ==== Option Explicit Sub SplitLongSpaces() Dim objRe As Object Dim colMatches As Object Const Pattern As String = "(\S+\s{1,3})+" Dim str As String Dim i As Long Set objRe = CreateObject("vbscript.regexp") objRe.Global = True objRe.Pattern = Pattern str = [a1].Text & " " If objRe.test(str) = True Then Set colMatches = objRe.Execute(str) For i = 0 To colMatches.Count - 1 Debug.Print Trim(colMatches(i)) Next i End If End Sub ======================================== --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
dividing an replacing spaces in string
It was to show that the data is never consistent based upon the whim
of the data entry person. I have had all kinds of weird things thrown in the cells. I was working along the lines of exactly what you had here Jason but regular expression ended up being a shorter bit of code for me to not get as lost in. Thanks for the help. scott On 25 Jan 2007 05:45:43 -0800, "Jason Lepack" wrote: Also, what happened to Yellow? On Jan 25, 5:47 am, scott d <please no email wrote: You guys always steer me right. How can I do this? I am showing spaces as ~ Everything is in a single cell. I don't need to do anything with the small groups of spaces 1-3 are ok. I need a Line break replacing the larger qtys. However they always vary in length. 2.31~QN~C/AS4~GAW~~~~~~~~~1.03~FW~YELLOW~~J/VVS13~~NEA~~~~~~~~~~~~40.04~HP~*F/VVS2~~~MHA ~~~~~~~~~~~~~~~~~~111.51~AD~D/SI~ EMO~~~~~~~~~~~1.20~MX~L/TS~ GHL~ So generally I need, when there is a large span of spaces to convert each group to a vbLf. This newly delimited string will dump into separate rows like this 2.31 QN C/AS4 GAW 1.03 FW J/VVS13 NEA 40.04 HP F/VVS2 MHA 111.51 AD D/SI EMO 1.20 MX L/TS GHL Any ideas? Thanks as always scott |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
dividing an replacing spaces in string
On Thu, 25 Jan 2007 14:47:50 -0800, scott d <please no email wrote:
ron, I knew regular expressions would help in this. This was exactly what I needed. I have this run through almost 30 columns of data and it is perfect. thanks so much scott Very glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing spaces with data from another cell | Excel Discussion (Misc queries) | |||
Replacing spaces with data from another cell | Excel Worksheet Functions | |||
Replacing spaces with a line end | Excel Discussion (Misc queries) | |||
Replacing spaces with zeros | Excel Programming | |||
replacing spaces? | Excel Programming |