how to eliminate commas at start of cell text?

**ribjig** · 08-25-2021, 12:56 PM

novice has columns with ~140K rows
keywords & phrases for photos in each cell,

e.g. ,word1 word2,word3 word4 word5 word6,word7 word8 word9, etc.

,word1 & word2, is a phrase surrounded by commas, no space needed after commas,
,word7 word8 word9, is also a phrase, e.g., New York City
(phrases strengthen ranking power of keywords)

characters per cell is limited, so eliminating unneeded characters an advantage;
commas at start of cell are unneeded, is there formula or steps to eliminate just those commas?

to make
,word1 word2,word3 word4 word5 word6,word7 word8 word9,
into
word1 word2,word3 word4 word5 word6,word7 word8 word9, ??

Thanks in advance. regards jg

**alidurfani** · 08-25-2021, 01:03 PM

For the value in cell A2

Try
=SUBSTITUTE(A1,",","",1)

**ribjig** · 08-25-2021, 01:06 PM

Thanks! Will try.

=SUBSTITUTE(I2,",","",1) worked!!

**alidurfani** · 08-25-2021, 02:18 PM

It would create problem if you don't have comma at starting of cell text
so to be on safe side

Try

=IF(LEFT(I2,1)=",",SUBSTITUTE(I2,",","",1),I2)

**TMS** · 08-25-2021, 01:06 PM

Try:

Formula:

=IF(LEFT(A2,1)<>",", A2, RIGHT(A2, LEN(A2)-1))

**TMS** · 08-25-2021, 03:16 PM

See post #3

**alidurfani** · 08-25-2021, 03:17 PM

Yes, after looking at your formula, I have changed mine

**JohnTopley** · 08-25-2021, 03:33 PM

No need to change as your original will work if there is no comma.

**ribjig** · 08-25-2021, 03:31 PM

Yikes.
Most cells don't have commas first.
First space got eliminated in those cases.
ChinaShanghai
SpainBarcelona
etc.
Unfortunately before discovering, I saved & closed.
Have backup copy dated Aug 19 but missing several unrelated changes made since.
Tried to find earlier version of today's copy via File>Info>ManageWorkbook
but nothing showed from before saving & closing...?

**TMS** · 08-25-2021, 03:36 PM

Unfortunately, we cannot help you recover your file. Lesson to be learned! Always test changes, particularly global changes, on a copy of the file

**TMS** · 08-25-2021, 03:45 PM

@John: no, the formula in post #2, and the amended version in post #4, will replace the first occurrence of the comma, even if it is not the first character. Looks like, in a lot of cases, it will combine two countries/cities.

The formula in post #3 tested for the scenario but was not used.

**alidurfani** · 08-26-2021, 11:05 AM

Just for my knowledge
Is there any problem with formula in post #5?

**ribjig** · 08-25-2021, 03:53 PM

When I am ready to retry...
=IF(LEFT(A2,1)<>",", A2, RIGHT(A2, LEN(A2)-1))
will eliminate cell first character only if comma
will not make any other changes to cell first characters or other cell characters
TIA

**TMS** · 08-25-2021, 03:58 PM

The formula checks to see if the formula is a comma; if not, it uses the whole of the cell value. If it is a comma, it takes the right-most characters in the cell.

Did you copy and paste values over the original cells before you saved and closed the file?

**ribjig** · 08-25-2021, 04:05 PM

No, unfortunately.
Inserted new column right of target column.
Dragged faulty formula to bottom new column.
Copied & Special Pasted Values new column over old column.
Saw that leading commas had disappeared.
Didn't think to look for rows without leading comma.
Saved & deleted new column.
Saved doc & closed.

**TMS** · 08-25-2021, 04:12 PM

**ribjig** · 08-26-2021, 10:33 AM

I am now using
=IF(LEFT(I2,1)<>",", I2, RIGHT(I2, LEN(I2)-1))
when there is nothing in target cell
this formula responds with a 0
is there way it could be modified to respond with nothing?
Thanks in advance

**AliGW** · 08-26-2021, 10:41 AM

A simple modification:

=IF(I2="","",IF(LEFT(I2,1)<>",", I2, RIGHT(I2, LEN(I2)-1)))

**ribjig** · 08-26-2021, 10:48 AM

Thanks but now the target cell that does have
text with comma first character is also showing
as nothing in formula cell & Special Paste Values
as nothing in target cell

**AliGW** · 08-26-2021, 11:00 AM

OK - without a sample workbook showing these issues, I have no chance of working it all out, sorry.

**AliGW** · 08-26-2021, 11:07 AM

Syntactically, no.

**alidurfani** · 08-26-2021, 11:09 AM

Ok thanks!

**ribjig** · 08-26-2021, 11:16 AM

=IF(LEFT(I2,1)=",",SUBSTITUTE(I2,",","",1),I2)
produces a 0 in formula cell if there is nothing
in target cell

I think I have solution
in target cells with nothing, replace nothing with nonsense word qwerty
then after using formula to eliminate unwanted comma first character
in target cells with text, replace qwerty in target cells with nothing...?

**alidurfani** · 08-26-2021, 11:32 AM

Formula in post #17 should work

You can also try

=IF(ISBLANK(I2),"",IF(LEFT(I2,1)=",",SUBSTITUTE(I2,",","",1),I2))

Hybrid View

how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Re: how to eliminate commas at start of cell text?

Thread Information

Users Browsing this Thread

Similar Threads

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions