Using Find & Replace - problems with repeated characters

[spon]

Hi all

I have a spreadsheet that contains cells with multiple lines - each line has been preceded with a roman numeral to reference the entry:

i. entry 1
ii. entry 2
iii. entry 3
etc

I have written a simple vba script to parse those lines and enter each individual entry into an adjacent cell in the same row but I am having limited success with that due to repeating characters - for example if I use InStr and search for "iv." and "v." to get the string between those two entries it gives a value of 1 as the difference as it finds "v." in the string "iv." on two occasions.

The other approach I thought about was to do a global find/replace in that column using the built in excel function. However, I have the same problem = if I do a global replace on, say, "i." with "1" then I get entries such as "i1." and "ii1." for what was roman ii. and iii.

Does anyone have any help on how to do exact string matching? I've tried using the options in find/replace to look for exact string but that doesn't work.

Any advice would be appreciate.

Thanks!

[philaugust2004]

Are you just wanting to split "ii. entry 2" into 2 columns or do you want to change "ii." into "2." also so it becomes "2. entry 2" in 2 columns??

[spon]

Hi philaugust - no just to replace the ii. with 2. to start with will be fine then I can run code based on straight numbers to split into the relevant columns.

Thanks

[sandy666]

how to do exact string matching?

source (A1)
iv. entry 1
vii. entry 2
v. entry 3

=RegExp_Replace(A1,"\b[v]{1}\b","5")

result
iv. entry 1
vii. entry 2
5. entry 3

but in this case it doesn't work for global but for single cell(s)

EDIT:
I forgot to say, you need dafine VBA code for this function

[JohnTopley]

Please Login or Register  to view this content.

From the web

to use

=Arabic("xxii")

String must be text