Forum Discussion
search and replace form "." to ","
Hello I'm a new one in this community.
My problem is that I want to do "search and replace" from "." to ","
I have a database of about 360,000 personal records in one file.
In my new file I use as an example a time in this format:
1.26,3 or 15.00,20 MM.sec,1000th seconds(HS) MM.SEC,1/100
In large old files, it's in 1.26.3 or 15.00.20.
Now I want to make in the old file of 1.26.3 the following format 1.26,3 or from 15.00.20 to 15.00,20.
You can't do that manually because it's a lot of data.
See below an example:
New file:
Froek Zwarthoed(name) 38,97 1.18,06 1.59,56 4.18,74 07.24,47 15.38,88
Old traffic jam:
Froek Zwarthoed(name) 38.97 1.18.06 1.59.56 4.18.74 7.24.47 15.38.88
I hope I have clearly described it...
Greetings,
Ivo
10 Replies
Does this do the trick you're looking for?
=SUBSTITUTE(B3,".",",",LEN(B3)-LEN(SUBSTITUTE(B3,".","")))ChrisMendoza , that's the text like
Froek Zwarthoed(name) 38.97 1.18.06 1.59.56 4.18.74 7.24.47 15.38.88
in one cell
Oh, that's interesting if it's all in one cell. Ivo1954 you could use Power Query split by delimiter " " (space) then do a similar substitution on the last "." then concatenate the values again.
Ivo, is the data like
Froek Zwarthoed(name) 38.97 1.18.06 1.59.56 4.18.74 7.24.47 15.38.88
in one cell as text or these are separate cells like
