Complicated right function

[marcvanderpeet12]

Guys,

I want to use a =Right() function on some text to extract the text from a word. All words are written like this " - Bank" and I want to transfer that to just "Bank". This can obviously be done by a simple =right(a2;4) function but problem is that the bank values differ. I also have

- Divisie
- International

etc... So i cant just use a normal right function. I need a function doing something like: get the third till the last letter.

Anybody help on how I can fix this?

Thanks!

Marc

 

[Somendra Misra]

Hi Marc,

Can you give say 5 examples of your data.

Regads,

 

[marcvanderpeet12]

I uploaded the file. It's about all the values in column G. I want to get rid of all the " - " before the texts...

 

[Somendra Misra]

You only want to replace " - " from column G and get the values in new column say
- Bestuursbureau
should become
Bestuursbureau
Regards,

 

[Hui]

If your going to be doing this regularly a formula like
=RIGHT(G2,LEN(G2)-FIND("- ",G2)-1)
will do the job

If your going to just do it once I'd use Text to Columns
Goto Data, Text to Columns, Fixed Width
Adjust Columns separators to suit

 

[shrivallabha]

One more formula approach which should work with your data:
=TRIM(SUBSTITUTE(G2,"-","",1))


Select Column G and do Find And Replace with
Find : " - " <--Without Quotes
Replace: Keep it blank
And do replace all.

 

[pecoflyer]

An for the lazy guys like me, select the cells to change - Ctrl+H
replace what "- " ( no quotes)
replace with ( leave blank)
Click replace All - Done

 

[shrivallabha]

An for the lazy guys like me, select the cells to change - Ctrl+H
replace what "- " ( no quotes)
replace with ( leave blank)
Click replace All - Done

Was wondering if that is any different than what I've suggested