Excel Help Please

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?

1.  replace the first "-" with ","?
2. replace the 10th character with a ","?
3. replace the 10th character with a "," but only if it is "-"?
4. remove all but the first "-"?
5. 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.

Posted by: thepurist

↑

^ 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 🤷