Searching and replacing "- "

[izhassan]

I have a worksheet that contains 900 rows of text data. In the column some of the text starts with a hyphen and space "- ". The others start with a number or alphabets. I am trying to compare this column to another one and find matches between them using IF(ISERROR(MATCH. I followed the thread

http://www.mrexcel.com/forum/excel-questions/923389-=if-iserror-match-question.html

and it helped. But I'm seeing that half of the data that should match is not because of this "- ". Is there somehow I can remove this? Also note that space hyphen space also appears in rows somewhere in the middle of the text. I don't want that to be replaced.

 

[FlameRetired]

If I follow correctly and assuming data starts in A2 try this formula

=IF(A2="","",IF(FIND("- ",$A$2:$A$9)=1,REPLACE($A$2:$A$9,1,2,""),$A$2:$A$9))

 

[FlameRetired]

I forgot to mention if this does part of the job how do you want it to work in/with MATCH?

 

[radian89]

Hi Izhassan,

block the column, ctrl + f, at replace tab,

find what = - (hyphen and space)
replace with = (don't fill anything)

you should make a backup column if you don't want the main column to change

 

[izhassan]

If I follow correctly and assuming data starts in A2 try this formula

=IF(A2="","",IF(FIND("- ",$A$2:$A$9)=1,REPLACE($A$2:$A$9,1,2,""),$A$2:$A$9))

Thanks. This works in removing the "- " from the start of the cell. However, for most cells it returns a #VALUE! error. Why is that?

 

[izhassan]

Hi Izhassan,

block the column, ctrl + f, at replace tab,

find what = - (hyphen and space)
replace with = (don't fill anything)

you should make a backup column if you don't want the main column to change

Thanks radian89. I've tried this before but it also replaces the hyphens and space which appear in between text. I just want to remove the hyphen space at the start of the text. For example...

"- The standard chunk of Lorem Ipsum used since the 1500s is reproduced below for those interested. - Sections 1.10.32 and - 1.10.33 from "de Finibus Bonorum et Malorum" by Cicero are also reproduced in their exact original form, accompanied by English versions from the 1914 translation by H. Rackham."

I don' t want to remove the hyphen space that appears before - Sections...

 

[FlameRetired]

@ #VALUE errors.
My bad. FIND is probably not a good choice here.

Try this instead

=IF($A$2:$A$11="","",IF(COUNTIF(A2,"- *")=1,RIGHT($A$2:$A$11,LEN($A$2:$A$11)-2),$A$2:$A$11))

 

[izhassan]

@ #VALUE errors.
My bad. FIND is probably not a good choice here.

Try this instead

=IF($A$2:$A$11="","",IF(COUNTIF(A2,"- *")=1,RIGHT($A$2:$A$11,LEN($A$2:$A$11)-2),$A$2:$A$11))

This works. It's a brilliant piece of code. Thanks FlameRetired. However, even after removing the hyphen space, the comparison using IF(ISERROR(MATCH is not producing the desired results.

 

[radian89]

Hi Izhassan,

try this

formula : =IF(LEFT(E5,1)="-",MID(E5,3,1000),E5)

[/IMG]

 

[FlameRetired]

However, even after removing the hyphen space, the comparison using IF(ISERROR(MATCH is not producing the desired results.

I guess I don't understand the context. Can you post the whole IF(ISERROR(MATCH formula you are having difficulty with?