asked on

How do i remove text in between commas in Excel?

Hi I need to remove names in data that is in a string in excel.
Examples
Currently looks like this:
780010084170,John Doe,780010084170.JPG
780010078876,Arnold Ziffel,780010078876.JPG
780010093031,Helen Hunt,780010093031.JPG

I want it to look like this:

780010084170 780010084170.JPG
780010078876 780010078876.JPG
780010093031 780010093031.JPG

...with a space inbetween the two numbers.

Thanks

Bill Prew

Do you want this as a formula, or a VBA bit of code?

~bp

Colleen Kayter

Based on your example, this information is all in a single cell.

You can find and replace:
Find: ,*,
Replace with: space (using spacebar, of course).

Professor J

try this. assuming your data starts in A1

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),1*99-98,99))&" "&TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),3*99-98,99))

Pawan Kumar

Hi,

Please try this ..

=SUBSTITUTE(F8,","," ")

Open in new window

Hope it helps!

Professor J

Pawan,

you misunderstood the question, OP does not want to remove comma, but he wanted to remove the first and last name in between the numbers, which my formula =TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),1*99-98,99))&" "&TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),3*99-98,99)) solves the problem for OP.

Shaun Vermaak

=MID(A1,1,FIND(",",A1,1)-1) & MID(A1,FIND(",",A1,FIND(",",A1,1)+1),LEN(A1)-FIND(",",A1,FIND(",",A1,1)+1)+1)

Open in new window

dlewis61

ASKER

OMG.... please read this update!! Now it's like this:

780010084170,John, Doe,780010084170.JPG
780010078876,Arnold, Ziffel,780010078876.JPG
780010093031,Helen, Hunt,780010093031.JPG

so there are multiple commas!! i still need it like this

780010084170 780010084170.JPG
780010078876 780010078876.JPG
780010093031 780010093031.JPG

Professor J

then try this one

=IFERROR(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),1*99-98,99))&" "&TRIM(MID(A1,SEARCH("^^",SUBSTITUTE(A1,",","^^",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))+1,LEN(A1))),"")

dlewis61

ASKER

ProfessorJimJam, this formula works in A1. I need to copy it all the way down the column...
how do i position the formula?

Shaun Vermaak

=MID(A1;1;FIND(",";A1;1)-1) & " " & MID(A1;1+FIND(",";A1;5+FIND(",";A1;1)+1);LEN(A1)-FIND(",";A1;FIND(",";A1;1)+1))

Open in new window

Saqib Husain

Without VBA or without formula

search/replace

,*, to ,

repeat this until there is only one comma in a line

then search/replace

, to <space>

ASKER CERTIFIED SOLUTION

Professor J

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dlewis61

ASKER

Great solution. Thanks!

Professor J

you are most welcome. i am glad i was able to help.