| View previous topic :: View next topic |
| Author |
Message |
WC
General User
Joined: 28 Nov 2007
Posts: 13
|
 Posted: Tue Jan 08, 2008 11:53 am Post subject: Find characters |
 |
|
Hello there!
I have a little problem with finding and than changing names. I have a lot of domains, for example:
who.irccyn.ec-nantes.fr.
And I have to get only: irccyn.ec-nantes.fr
But it bothers me the dot at the and of domain, If there wouldn't be . than this formula would be ok: =RIGHT(B1;LEN(B1)-FIND(".";B1)) but now that there is a dot at the end I don't know how to resolve this. Any help would be appreciated! |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Tue Jan 08, 2008 12:00 pm Post subject: |
|
|
| Code: |
=IF( RIGHT(B1;1)="." ; LEFT(B1 ; LEN(B1)-1) ; B1)
|
|
|
| Back to top |
|
|
WC
General User
Joined: 28 Nov 2007
Posts: 13
|
| Posted: Tue Jan 08, 2008 12:04 pm Post subject: |
|
|
| Villeroy wrote: | | Code: |
=IF( RIGHT(B1;1)="." ; LEFT(B1 ; LEN(B1)-1) ; B1)
|
|
Well I thought without first word and dot, like this: irccyn.ec-nantes.fr as with this formula above I get who.irccyn.ec-nantes.fr |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Tue Jan 08, 2008 12:32 pm Post subject: |
|
|
Your formula removes the leading "who.", mine removes the dot at the end.
The combination of both formulas is:
B1: who.irccyn.ec-nantes.fr.
C1: =RIGHT(B1;LEN(B1)-FIND(".";B1))
D1: =IF(RIGHT(B1;1)=".";LEFT(C1;LEN(C1)-1);C1)
Replacing C1 with the formula in C1 we get an ugly monster:
=IF(RIGHT(B1;1)=".";LEFT(C1;LEN(RIGHT(B1;LEN(B1)-FIND(".";B1)))-1);RIGHT(B1;LEN(B1)-FIND(".";B1))) |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Tue Jan 08, 2008 2:06 pm Post subject: Re: Find characters |
|
|
Save as a CSV and use freeware CSVed. It's dead simple, working on an entire column(s) at a time.
David. |
|
| Back to top |
|
|
|
