CODEWITHSUNDEEP
excelexcel-formula
phownz
phownz

Reputation: 1

Excel add same prefix to two values within one cell

not sure if this can even be done, Google search has not been too helpful (mainly due to the fact its just returning how to add a prefix)

Problem:

I have one column in Excel with two comma separated values - for arguments sake, "Bill, Ted" in A1 and I'd like to add a prefix to BOTH of the names, lets just say the word "excellent"

What I have so far looks like this and yields the correct result but only for the first value, so is there a way I can define it to attach the prefix to the next value?

=CONCATENATE("excellent",A1) - "excellentBill, Ted"

So my desired result would be:

"excellentBill, excellentTed"

I'm sure its pretty straighforward but I've been trying different approaches with CONCATENATE and SUBSTITUTE and just can't seem to get the desired result. I was wondering if you can prepend a LEN FUNCTION but wondered if there is a simpler approach?

Any help is appreciated.

Thanks.

Upvotes: 0

Views: 391

Answers (1)

Mark S.
Mark S.

Reputation: 2596

Try this. I'm finding the delimiter in the cell then building around that.

="Excellent" & left(A1, Find(",",A1))& "Excellent" & right(A1, len(A1) -Find(",",A1))

=Concat("Excellent",left(A1, Find(",",A1)),"Excellent",right(A1,len(A1) - Find(",",A1)))

Upvotes: 2

Related Questions