How to replace certain character in a string

Question

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

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:

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

FunThomas · Accepted Answer · 2020-10-12 11:54:04Z

4

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

edited Oct 12, 2020 at 11:54

answered Oct 12, 2020 at 11:26

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Filip Frątczak Over a year ago

But abbreviations aren't determined in any way, so user can put there anything. If I only knew all the abbreviations which will be used, I could put them into an array and search for each element of the array and replace it.

Mr.Burns Over a year ago

I had a feeling you could RegEx this question, but I haven't used it enough to even come close to answering a question that needs it

T.M. Over a year ago

@FunThomas Just for fun ... posted an alternative to your valid answer :+)

Mr.Burns · Accepted Answer · 2020-10-12 12:38:01Z

2

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

edited Oct 12, 2020 at 12:38

answered Oct 12, 2020 at 12:00

Mr.Burns

7001 gold badge10 silver badges25 bronze badges

3 Comments

Filip Frątczak Over a year ago

I guess it wont work, if values in cell will have different lenght. Eg. 100 kg 100.55 kg. As well it will work for same pattern like 10 kg 20 kg 11 kg etc. Am I correct?

Mr.Burns Over a year ago

@FilipFrątczak Character length of the number or the abbreviations IE 100 kgs 100.55 kgg wont matter as it just looks for the white space ` ` this should work for everything unless someone puts a white space that isn't a white space. However I have released I have made a mistake, and the code is updated for it

T.M. Over a year ago

Fine to use the modulo logic +)

T.M. · Accepted Answer · 2020-10-13 15:04:29Z

0

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.

edited Oct 13, 2020 at 15:04

answered Oct 13, 2020 at 11:10

T.M.

10k3 gold badges40 silver badges61 bronze badges

2 Comments

FunThomas Over a year ago

I use VBA since a long time, but never saw the filter function before - good to know. However, I see 2 issues: a) Function fails if space is missing between number and abbreviation (see screenshot from OP: 30.5kg) and b) will work only if the abbrev. is the same for all values.

T.M. Over a year ago

Appreciate feedback and edited my answer. - BTW I'm glad I could demonstrate a new function and enjoying to find alternatives to existing solutions as this isn't only fun :-), but possibly helpful for number of users - @FunThomas

Collectives™ on Stack Overflow

How to replace certain character in a string

3 Answers 3

3 Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related