Remove symbols only in certain segments of a string

[Magic_Doctor]

Hello,


Suppose that in my chain there is:
"phone.numbers: 365.23.55 & 222.55.47 & 06.32.55.88"
I would like to delete the "." only for phone numbers and replace them with a space, to get this:
"phone.numbers: 365 23 55 & 222 55 47 & 06 32 55 88"


Thank you in advance for any suggestions.

 

[StephenCrump]

How will it be possible to identify the end of the "phone.numbers" sub-string, e.g. can we rely on there being a particular heading? A particular separator? Or perhaps just an alpha (rather than numeric) character?

Can you illustrate with a few examples?

 

[jtakw]

Hi,

Based on your sample, this will do it:

Book1
	A	B	C
1	"phone.numbers: 365.23.55 & 222.55.47 & 06.32.55.88"		"phone.numbers: 365 23 55 & 222 55 47 & 06 32 55 88"
Sheet23

Cell Formulas
Range	Formula
C1	=SUBSTITUTE(SUBSTITUTE(A1,"."," ")," ",".",1)

 

[Magic_Doctor]

Hello StephenCrump,

Good question !

Condition: All phone numbers must be separated by anything ("+", "and", "&", "Zorro", ...) except only one space.
Example: 023 56 99 32 & 52 33 77 88 zorro 5258 32 66 + 094 54 20 44
The end of the phone number will necessarily be the first character that is obviously neither a number nor a space.
I think maybe the best way to do this is through "VBScript.RegExp" (which I do not control at all); in short, try to develop the right "Pattern".

 

[Magic_Doctor]

Hello jtakw,


I only put one example "for the example". The chain could very well have been: "Manu.la.macronade.lets.me know. that.I.can.reach.him.at 033.55.88.66 or 562.258.33 or 752.336.22 tomorrow.morning.before.the.sun.goes.down"
The chain is eminently variable.

 

[StephenCrump]

I'm more interested in whether there you have "." characters somewhere to the right of the Phone numbers sub-string that you don't want changed to " ".

I know the English/French separators are different, but perhaps something like:

phone.numbers: 365.23.55 & 222.55.47 & 06.32.55.88 ........... Amount1 $123.45, Amount2 $67.89

where I don't want the decimal points in the amounts to change.

EDIT: Didn't see your last post. Are we always changing "n.n.n" to "n n n", and "n.n.n.n" to "n n n n", where n is a variable number of digits 0-9?

 

[Magic_Doctor]

Bonjour StephenCrump,

A telephone number can be considered as a series of groups of numbers separated by a space or something else (".", "/", "|" ...) according to the habits of each, although in France, in fact, one usually separates groups of digits from a telephone number by a simple space (042 26 12 41).
I chose the example of the point (".") Because it is an integral part of a sentence. The dot ("."), like the comma (",") are important. If we decide to remove all points (or commas) from a string, it is not very complicated ("Replace ..."). On the other hand, assuming that the person who wrote the phone numbers has separated the groups of numbers by one point (it is his habit), if we replace all the points of the numbers by a space, necessarily the points that are outside the numbers will also be substituted by a space.

 

[István Hirsch]

Try this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0.","0 "),"0.","1 "),"2.","2 "),"3.","3 "),"4.","4 "),"5.","5 "),"6.","6 "),"7.","7 "),"8.","8 "),"9.","9 ")