Using Replace to only replace a small portion of a string

Question

I am reading data from an text file, and I am trying to format it to match a Defined Name for a cell. The defined name is 89NL_10ETH_A and the string is 89NL 10ETH A but with a bunch of spaces after it. I used Replace() to convert spaces into "_", but all of the spaces at the end are converted to underscores. How can I trim the spaces off of a fixed length, mind you Trim() did not work because of the fixed length?

This is how I am currently doing it

getProduct = Replace(Replace(Mid(Ln, 40, 24), "#", ""), "%", "")

Application.Trim I believe works for VBA. Instead using the replace, after the Trim you could use Split(STR, " ")and finally Join(arrSTR, "_") — Damian
– Damian, Commented Mar 20, 2019 at 15:01
Trim() works. Use it first, before replacing the remaining spaces. — Andy G
– Andy G, Commented Mar 20, 2019 at 15:04
I see what i was doing wrong. I had the Mid length too long and it was picking up a period from the next column that i wasn't seeing, and why it looked like my Trim wasn't working. — Geoffrey
– Geoffrey, Commented Mar 20, 2019 at 16:48

Pᴇʜ · Accepted Answer · 2019-03-20 15:06:55Z

2

Just Trim before you Replace:

Sub TrimTest()

    Dim InputString As String
    InputString = "  89NL 10ETH A    "

    Debug.Print Replace$(Trim$(InputString), " ", "_")

End Sub

Trim removes the spaces from " 89NL 10ETH A " in the beginning and end "89NL 10ETH A" so the spaces in between can be replaced by underscore "89NL_10ETH_A".

answered Mar 20, 2019 at 15:06

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

2 Comments

Andy G Over a year ago

There is RTrim as well but the difference is probably minuscule.

Pᴇʜ Over a year ago

@AndyG Yes but just in case the OP needs to trim both. I would use LTrim or RTrim only in the rare cases where either left or right spaces should not be removed.

GMalc · Accepted Answer · 2019-03-20 15:40:44Z

1

As @AndyG stated, you can use Replace() with Trim().

You can use it with a text string

getProduct = Replace(Trim("89NL 10ETH A           "), " ", "_")
MsgBox getProduct

Or, with a cell value

getProduct = Replace(Trim(Range("A1").Value), " ", "_")
MsgBox getProduct

answered Mar 20, 2019 at 15:40

GMalc

2,6381 gold badge11 silver badges16 bronze badges

1 Comment

Geoffrey Over a year ago

This correct. It is what I already had in place, but the Mid length was too long and i was picking up a period that I did not see, which gave the illusion that Trim() wasn't working.

Damian · Accepted Answer · 2019-03-20 15:05:21Z

0

This way works fine:

Sub Test()

    Dim STR As String, arrSTR

    STR = "89NL 10ETH A    "
    arrSTR = Split(Application.Trim(STR), " ")
    MsgBox Join(arrSTR, "_")


End Sub

answered Mar 20, 2019 at 15:05

Damian

5,1741 gold badge12 silver badges22 bronze badges

9 Comments

Andy G Over a year ago

Why split and join when you can just replace?

Damian Over a year ago

Thought replace is a worksheetfunction, so doing that was faster?

Pᴇʜ Over a year ago

@Damian Well there is the WorksheetFunction.Replace method (Replaces part of a text string, based on the number of characters you specify, with a different text string.) and the Replace function (Returns a string, in which a specified substring has been replaced with another substring), but they do completely different things if you compare the documentation carefully.

Andy G Over a year ago

I wouldn't know without investigating.

Damian Over a year ago

@Pᴇʜ i've checked, yes... the worksheetfunction works for cells and the replace function works for a single string. There is something i've learn today

|

Collectives™ on Stack Overflow

Using Replace to only replace a small portion of a string

3 Answers 3

2 Comments

1 Comment

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related