Re: 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

<ronrosenfeld@xxxxxxxxxx> 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
.

Relevant Pages

  • Re: Extract email addresses
    ... Because of a difference in the VBA flavor of Regular Expressions, ... Function REMid(str As String, Pattern As String, _ ... Dim objRegExp As RegExp ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Extract Number from text
    ... First set a reference to Microsoft VBScript Regular Expressions 5.5 ... Function GetNumber(stringVal As String) As Double ... Dim regEx, Match, Matches ' Create variable. ...
    (microsoft.public.excel.programming)
  • Re: Excel formula needed... regular expression?
    ... Function ExtrConcat(str As String, sPattern As String, _ ... 'Requires setting a Reference to Microsoft VBScript Regular Expressions 5.5 ... Dim mc As MatchCollection ... ExtrConcat = ExtrConcat & ma & sSeparator ...
    (microsoft.public.excel.misc)
  • Re: Regular Expressions in Access
    ... Public Function IsAlphaNumericAs Boolean ... Dim sTemp As String ... > unfortunately I can't remember whether I covered using regular expressions ...
    (microsoft.public.access.queries)
  • Re: dividing an replacing spaces in string
    ... This newly delimited string will dump into separate rows like this ... Dim colMatches As Object ... Set objRe = CreateObject ...
    (microsoft.public.excel.programming)