Removing blank space from a string

regex excel vba

186 观看


33 作者的声誉

How do I remove a blank space in a return string?

I am using in-cell functions in Excel. I find text in a string and move it to another place in the same string.

My results include an extra blank space that I want to remove.

Sample text string:

999999 - Blah Blah Blah 8888 blah blah

Desired result is:

999999 (8888) - Blah Blah Blah blah blah

My actual result is (note the extra blank space at 3rd Blah)

999999 (8888) - Blah Blah Blah  blah blah

Here is my current relevant code:

strPattern = "^(\d{6} )(\- )(\D+)(\d{4})"
strReplace = "$1($4) $2$3"

How do I deal with this either through my strPattern definition and/or strReplace definition?

作者: Tom F 的来源 发布者: 2017 年 12 月 27 日

回应 1


361690 作者的声誉


You may adjust your regex pattern to match as few non-digit symbols before optional whitespace chars as possible:

^(\d{6} )(- )(\D*?)\s*(\d{4})

See the regex demo


  • ^ - start of the string
  • (\d{6} ) - Group 1: six digits
  • (- ) - Group 2: a hyphen and a space
  • (\D*?) - Group 3: any 0+ chars other than digits, but as few as possible as the *? quantifier is lazy (the quantified pattern is only tried and expanded when the subsequent patterns fail to match)
  • \s* - 0+ whitespace chars
  • (\d{4}) - Group 4: four digits.
作者: Wiktor Stribiżew 发布者: 2017 年 12 月 27 日