Manipulate String 2

[Iamthestig]

Hi,

can anyone help me convert this string:

'German. Speaks Spanish Fluently, Dutch Fluently, English Fluently, Italian Advanced, French Advanced.'

INTO

'German. Speaks Spanish, Dutch, English Fluently, Italian, French Advanced.'

The string could be any combination of languages, the only constants being 'Fluently' always precedes 'Advanced'. So I really want to replace all instances of 'Fluently' before the final 'Fluently' and all instances of 'Advanced' except the final 'Advanced'.

So 'Speaks Spanish Fluently, Italian Advanced' remains unchanged but 'Speaks Spanish Fluently, Dutch Fluently, English Advanced, Italian Advanced, French Advanced.' becomes
'Speaks Spanish, Dutch Fluently, English, Italian, French Advanced.'

Many thanks

 

[PHV]

While InStr(yourString, "Fluently") <> InStrRev(yourString, "Fluently"): yourString = Replace(yourString, " Fluently", "", 1, 1): WEnd
While InStr(yourString, "Advanced") <> InStrRev(yourString, "Advanced"): yourString = Replace(yourString, " Advanced", "", 1, 1): WEnd

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DaveInIowa]

Very elegant solution PHV! Nice and concise.

 

[Iamthestig]

Works perfectly PH, thanks alot. Have a star!

 

[Iamthestig]

Ok, after showing the results to the user, they have changed their mind! I now need to convert

'German. Speaks fluent Spanish, fluent Dutch, fluent English, advanced Italian, advanced French.'

Into

'German. Speaks fluent Spanish, Dutch, English, and advanced Italian, French.'

So delete all occurences of 'fluent' except the first and likewise with 'advanced' (and insert 'and ' before 'advanced')

I have tried to modify PH's code above but no success yet.

Any help much appreciated.

Thanks.

 

[Iamthestig]

So I thought I could do this:

While InStr(sSpeaks, "fluent") <> InStrRev(sSpeaks,"fluent"):sSpeaks = Replace(sSpeaks,"fluent", "",CLng(InStrRev(sSpeaks,"fluent")), 1): Wend
While InStr(sSpeaks, "advanced") <> InStrRev(sSpeaks,"advanced"):sSpeaks = Replace(sSpeaks,"advanced ", "",CLng(InStrRev(sSpeaks,"advanced")), 1): Wend

But it replaces all the text before the Count in the Replace function, not the word 'fluent ' from the count onwards!

 

[xlbo]

Love the brevity of your code PHV!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

Love the brevity of your code PHV
You're right, I am not talkative

 

[xlbo]

you may not be talkative but you do write some beautiful code!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

sSpeaks = Replace(Replace(Replace(sSpeaks, ", fluent", ","), ", advanced", " and advanced", 1, 1), ", advanced", ",")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886