Can some on help with a formula to change a charcter from - to ,
I need the forumla to change the character after 10 charceters from the left of the cell
Thanks in advance
Is it always the 10th from the left? If so you can simply do:
=REPLACE(A1, 10, 1, ",")
Alternatively, this will replace the first instance of "-" with ","
=SUBSTITUTE(A1, "-", ",", 1)
=replace(A1,1,10,",")
ok the subsitue seems to be working better but it only change 1 instance of it in a cell i want it to keep the 1st istnace of it and remove the others i.e
=SUBSTITUTE(A1, "-", ",", 1)
help - with - this - Formula
would become
Help - with this Formula
Erm ok that's a bit trickier.
The last number in the substitute function is which instance of the character to replace, so you can do eg:
=SUBSTITUTE(A1, "-", ",", 2)
to just replace the second one. To replace all dashes with commas EXCEPT the first one, um... I will cheat and resort to AI 😁
Gemini gives me this, which looks insane but works nicely:
=LET(
text_string, A1,
char_to_replace, "-",
replacement_char, ",",
first_occurrence, FIND(char_to_replace, text_string),
part_before, LEFT(text_string, first_occurrence),
part_after, MID(text_string, first_occurrence + 1, LEN(text_string) - first_occurrence),
replaced_after, SUBSTITUTE(part_after, char_to_replace, replacement_char),
result, part_before & replaced_after,
result
)
Edit: Does that help? It seems somewhat at odds with your original requirements.
Edit 2: Another way of doing it, which is simpler but stupider 😁
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "-", "¶", 1), "-", ","), "¶", "-", 1)
You can now do regex in excel.
writing good regex requires a very clear spec.
eg “replace all - in the text except the first one” where does the 10th character come in? Example test cases would be useful.
very clear spec
you need to be mighty sure that you're happy to lose ALL other hyphens, minuses that may be lurking in there
What are you actually trying to do?
- replace the first "-" with ","?
- replace the 10th character with a ","?
- replace the 10th character with a "," but only if it is "-"?
- remove all but the first "-"?
- replace the first "-" with a "," and remove all other "-"s?
^ is exactly why people hated giving a brief to my analytics team. We'd always come back with a list of things to clarify, half of which the originator didn't have an immediate (or sometimes ever) answer to.
^ is exactly why people hated giving a brief to my analytics team. We'd always come back with a list of things to clarify, half of which the originator didn't have an immediate (or sometimes ever) answer to.
I work in software development. I feel your pain.
To do what you have asked and spec'd out then
= Left (a1, 10) & Substitute (Mid (a1, 11, 999999), " -", " ")
To do what you need 🤷
