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

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

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

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

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

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

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