Exclamation Delete/cut some text in cell

**jagocoding** · 02-27-2012, 06:27 AM

hello all..
i confused with this problem.
cut or delete some text in cell...

i have 1.000.000 rows , wanna remove some text in cell..
example :

st. causeway bay kav.19 hongkong 12345
st. harbour rd.19 hongkong 12345
st. yamatee north rd.29 hongkong 12345

must change to :

- street. causeway bay hongkong 12345 --> kav.19 removed
- street. harbour rd.19 hongkong 12345 --> rd.19
- street yamatee north hongkong 12345 --> rd.29 removed

only street name and country name also zip code will be posted, number road and other text word must removed.

st to street can use replace function , but how to remove text number, road, and other text word.

any function or logic to do this ???.. please help me friends. this my first job at office.
i attached file here. "
thanks be4

**Richard Buttrey** · 02-27-2012, 06:35 AM

Hi,

We need to define a rule or rules in order to do this.
Looking at your admittedly limited data one rule might be

1. Find the second occurrence of the '.'
2. Then find the characters between the first space immediately preceding the '.' and the first space immediately after the '.'.
3. Delete all the characters between the two spaces and one of the spaces.
4. Change 'st' to 'street'

But before spending time responding to this further you need to tell us whether this rule would work for all your data. And if not show other records where it would not apply and hence a second or modified rule would be required. .....over to you.

Is there also a mistake in your example? Why aren't you removing the 'rd.19' in the second record? If you don't want to delete this then obviously the suggested rule above won't work, so you need to tell us why apart from the change of street, the string remains the same.

Regards