CODEWITHSUNDEEP
excelvba
Filip Frątczak
Filip Frątczak

Reputation: 157

How to replace certain character in a string

I am trying to replace not each space in a single string with line break. String is taken from specific cell, and looks like:

enter image description here

Now, Im trying to replace each space after abbreviation to line break. The abbreviation can be any, so the best way for precaching which space I intend to replace is like: each space after number and before a letter?

The output I want to get is like:

enter image description here

Below is my code, but it will change every space to line break in cell.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    On Error GoTo Exitsub
    
    If Not Intersect(Target, .Columns(6)) Is Nothing Then
        Application.EnableEvents = False
        Target.Value = Replace(Target, " ", Chr(10))
    End If
    Application.EnableEvents = True
    
Exitsub:
    Application.EnableEvents = True
    
End Sub

Upvotes: 0

Views: 167

Answers (3)

T.M.
T.M.

Reputation: 9948

Identify arbitrary abbreviation first

"abbreviations aren't determined ..."

Knowing the varying abbreviation which, however is the same within each string (here e.g. kg ) actually helps following the initial idea to look at the blanks first: but instead of replacing them all by vbLf or Chr(10), this approach

  • a) splits the string at this " " delimiter into a zero-based tmp array and immediately identifies the arbitrary abbreviation abbr as second token, i.e. tmp(1)
  • b) executes a negative filtering to get the numeric data and eventually
  • c) joins them together using the abbreviation which is known now for the given string.

So you could change your assignment to

    '...
    Target.Value = repl(Target)      ' << calling help function repl()

Possible help function

Function repl(ByVal s As String) As String
'a) split into tokens and identify arbitrary abbreviation
    Dim tmp, abbr As String
    tmp = Split(s, " "): abbr = tmp(1)
'b) filter out abbreviation
    tmp = Filter(tmp, abbr, Include:=False)
'c) return result string
    repl = Join(tmp, " " & abbr & vbLf) & abbr
End Function

Edit // responding to FunThomas ' comment

ad a): If there might be missing spaces between number and abbreviation, the above approach could be modified as follows:

Function repl(ByVal s As String) As String
'a) split into tokens and identify arbitrary abbreviation
    Dim tmp, abbr As String
    tmp = Split(s, " "): abbr = tmp(1)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'b) renew splitting via found abbreviation (plus blank)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
    tmp = Split(s & " ", abbr & " ")
'c) return result string
    repl = Join(tmp, abbr & vbLf): repl = Left(repl, Len(repl) - 1)
End Function

ad b): following OP citing e.g. "10 kg 20 kg 30,5kg 15kg 130,5 kg" (and as already remarked above) assumption is made that the abbreviation is the same for all values within one string, but can vary from item to item.

Upvotes: 0

Mr.Burns
Mr.Burns

Reputation: 700

The below will replace every 2nd space with a carriage return. For reason unknown to me The worksheet function Replace will work as intended, but the VBA Replace doesnt

This will loop through every character in the defined area, you can change this to whatever you want.

The if statement is broken down as such

(SpaceCount Mod 2) = 0 this part is what enable it to get every 2nd character. As a side note (SpaceCount Mod 3) = 0 will get the 3rd character and (SpaceCount Mod 2) = 1 will do the first character then every other character

Cells(1, 1).Characters(CountChr, 1).Text = " " is to make sure we are replacing a space, if the users enters something funny that looks like a space but isn't, that's on them

I believe something like this will work as intended for you

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error GoTo Exitsub

Application.EnableEvents = False

For CountChr = 1 To Len(Target.Value)
    If Target.Characters(CountChr, 1).Text = " " Then
        Dim SpaceCount As Integer
        SpaceCount = SpaceCount + 1
        If (SpaceCount Mod 2) = 0 Then
            Target.Value = WorksheetFunction.Replace(Target.Value, CountChr, 1, Chr(10))
        End If
    End If
Next CountChr


Application.EnableEvents = True

Exitsub:
Application.EnableEvents = True

    
End Sub

Upvotes: 2

FunThomas
FunThomas

Reputation: 29146

You can try

Target.Value = Replace(Target, "kg ", "kg" & Chr(10))

If you can have other abbreviations like "g" or "t", do something similar for them (maybe in a Sub), just be cautious with the order (replace first "kg", then "g")

Update: If you don't know in advance the possible abbreviations, one attempt is to use regular expressions. I'm not really good with them, but the following routine seems to do:

Function replaceAbbr(s As String) As String
    Dim regex As New RegExp
    regex.Global = True
    regex.Pattern = "([a-z]+) "
    replaceAbbr = regex.Replace(s, "$1" & Chr(10))
End Function

Upvotes: 4

Related Questions