Regular Expression - To match a closest space after nth character

Chlwls808

New Member
Joined
Jun 20, 2021
Messages
47
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I'm trying to workout something and in need assistance on setting RegEx pattern.

What would the regular expression pattern be if I were to match a certain space in a string.

As an example, I have a string:

"Mary had a little lamb"

And I want to match the closest space to the left, after 20th character. This would be the space between the "e" and the "l". So far I have this (?<=\s) and I can't figure out anything else with it.

Thank you.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,345
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
What do you mean by "match the closest space"... do you want the position number of that space?

You said "I want to match the closest space to the left, after 20th character" and I note the words "after the 20th character" but your description of the space you want to match is before the 20th character, not after it... please clarify.
 

Chlwls808

New Member
Joined
Jun 20, 2021
Messages
47
Office Version
  1. 2016
Platform
  1. Windows
Yes, I guess it does sound confusing. Apologize for that. I meant before the 20th character.

So, I'm a newbie in macro and I'm tackling something bit complex for my standard, but trying to do as much as I can on my own rather than asking the pro's here for the end result. Hence why the question became that way.

But where I'm stuck is a part where I'm I am trying to get the regular expression pattern to identify the nearest space so that I can add a delimiter, without breaking a whole-word, only when the string is 20th character and longer.

My expectation would be to get:

FromTo
Mary had a little lambMary had a little@lamb
London bridge is falling downLondon bridge is@falling down
This little piggieThis little piggie
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,238
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
no need RegExp as it is easily doable with VBA basics …​
 

Forum statistics

Threads
1,141,130
Messages
5,704,454
Members
421,350
Latest member
jake9951

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top