Check if cell starts with certain character

**ericc** · 09-09-2010, 12:35 AM

Hi everyone. I need a little help. I have a column full of text. Some cells start with a "- " (Hyphen space) others do not. There could be other hyphens throughout the cell but I am only interested in if starts with "- " Here is an example of a few cells

Cell # text in cell
aq56 - Cortelco - Volume - Tone/pulse - Ringer
aq57 Cortelco - Volume - Tone/pulse - Ringer

See how aq56 started with a "- " but aq57 does not
I am going to be taking out the "- " with =MID(AQ56,3,65000) but if the cell doesn't have the "- " then it ends up stripping out the first to characters. Basically I need some sort of IF formula that says IF the cell STARTS with "- " then do the MID function, otherwise just copy the cell. I just want to be sure that the formula doesn't pay attention to the later occuring "- ". I am guessing a LEFT function would work but I am brain locked and can't get a working formula out.

I prefer this to be a formula, not a VBA. I will probably be adding other conditions later and I can usually build off a formula but i dont know programming at all.

Thank you so much for your help!!!

Eric

**Paul** · 09-09-2010, 12:38 AM

Hi Eric,

You can use

=IF(LEFT(AQ56,2)="- ",MID(AQ56,3,65000),AQ56)

Alternatively, you might try

=IF(LEFT(AQ65,2)="- ",RIGHT(AQ56,LEN(AQ56)-2),AQ56)

**ericc** · 09-09-2010, 02:13 AM

Hi Paul. Thanks a lot. The first one works great for the lines I tried. This is really going to same me a lot of time!!! I have over 4000 cells and checking each one individually was driving me nuts!!!

Is there anyway to have worksheet 1 (which is full of formulas) copy only the values to worksheet 2?

Thanks again for the formula!!

**Paul** · 09-09-2010, 01:24 PM

Select the cells you want to copy on Sheet1 and press CTRL+C (or right-click and Copy, or use the Copy button). Go to Sheet2, right-click the cell you want to add the copied data to and choose PasteSpecial. In the dialog that appears, select Values and click OK.

Glad I could help you out.

**ericc** · 09-10-2010, 04:35 PM

Sorry, I didn't put in enough info. I meant, is there a way to take the data from worksheet 1 into worksheet 2 (values only) automatically. no copy/paste special values but with the same results.

Thanks again!
Eric