How to determine the first two carachters in a name

hellZg8 · 04-17-2006, 02:20 AM

is there a way to deterimine the first two characters in a name for abbrev.

eg. Charlie Brown result =CH
thanks in advance for any help

**CaptainQuattro** · 04-17-2006, 02:29 AM

If the name is in cell A1:

=Left(A1,2) will return the two leftmost characters.

similarly Right (A1,2) will give you the last two characters.

If you want to locate characters in the middle of a name, or any other string, use =MID

e.g. =MID(A1,2,2) will give you the second and third characters in the string.

**ckc_2k** · 04-17-2006, 02:47 AM

great. here i have one question for you that i need to remove space between two words in same cell(A1) eg; K OSTAS CON STAN TINOU result should be KOSTASCONSTANTINOU. is it possiable thing??

thanks
ckc.

hellZg8 · 04-17-2006, 02:50 AM

This in fact did thank you for your time and effort

"CaptainQuattro" wrote:

>
> If the name is in cell A1:
>
> =Left(A1,2) will return the two leftmost characters.
>
> similarly Right (A1,2) will give you the last two characters.
>
> If you want to locate characters in the middle of a name, or any other
> string, use =MID
>
> e.g. =MID(A1,2,2) will give you the second and third characters in the
> string.
>
>
> --
> CaptainQuattro
> ------------------------------------------------------------------------
> CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
> View this thread: http://www.excelforum.com/showthread...hreadid=533357
>
>

**starguy** · 04-17-2006, 03:17 AM

Hi ckc!

its easy to do. just select cell A1
press Ctrl+H, it will open find and replace window.
in Find what: just press space bar
and leave Replace with: empty. and at last press replace.
where are the spaces...

you can also use a formula to do this
=substitute(A1," ","")

Bob Phillips · 04-17-2006, 04:40 AM

=SUBSTITUTE(A1," ","")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ckc_2k" <[email protected]> wrote in
message news:[email protected]...
>
> great. here i have one question for you that i need to remove space
> between two words in same cell(A1) eg; K OSTAS CON STAN TINOU result
> should be KOSTASCONSTANTINOU. is it possiable thing??
>
> thanks
> ckc.
>
>
> --
> ckc_2k
> ------------------------------------------------------------------------
> ckc_2k's Profile:
http://www.excelforum.com/member.php...o&userid=33553
> View this thread: http://www.excelforum.com/showthread...hreadid=533357
>

Dave Peterson · 04-17-2006, 08:00 AM

Another way if you have lots to do and don't want to use formulas in helper
cells.

Select the range
edit|replace
what: (spacebar)
with: (leave blank)
replace all

ckc_2k wrote:
>
> great. here i have one question for you that i need to remove space
> between two words in same cell(A1) eg; K OSTAS CON STAN TINOU result
> should be KOSTASCONSTANTINOU. is it possiable thing??
>
> thanks
> ckc.
>
> --
> ckc_2k
> ------------------------------------------------------------------------
> ckc_2k's Profile: http://www.excelforum.com/member.php...o&userid=33553
> View this thread: http://www.excelforum.com/showthread...hreadid=533357

--

Dave Peterson