Prev: Three questions about fields in worksheets
Next: Create menu with Workbook_open : error
From: sam on 13 Apr 2010 17:54
Hi All,

How can I extract First letter from first name, remove the space between
first and last name and keep the last name?

For eg:
I have a list of Names in this format:
John Doe
Will Smith

and I want them like this:
jdoe
wsmith

Thanks in advance
From: Mike H on 13 Apr 2010 18:33
Sam,

Try this and drag down

=LEFT(A2,1)&MID(A2,FIND(" ",A2)+1,999)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"sam" wrote:

> Hi All,
>
> How can I extract First letter from first name, remove the space between
> first and last name and keep the last name?
>
> For eg:
> I have a list of Names in this format:
> John Doe
> Will Smith
>
> and I want them like this:
> jdoe
> wsmith
>
> Thanks in advance
From: Paul on 13 Apr 2010 18:21

Try:

=LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,255)

Assuming A1 holds the text "John Doe". Fill down as many rows as
necessary.




s
a
m
;
6
9
8
4
6
5

W
r
o
t
e
:


>
Hi All,
>
> How can I extract First letter from first name, remove the space
between
> first and last name and keep the last name?
>
> For eg:
> I have a list of Names in this format:
> John Doe
> Will Smith
>
> and I want them like this:
> jdoe
> wsmith
>
> Thanks in advance


--
Paul

- Paul
------------------------------------------------------------------------
Paul's Profile: http://www.thecodecage.com/forumz/member.php?u=1697
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=195384

http://www.thecodecage.com/forumz

From: Otto Moehrbach on 13 Apr 2010 18:42
Try this:
=LOWER(LEFT(A1,1)&RIGHT(A1,LEN(A1)-FIND(" ",A1)))
Otto
"sam" <sam(a)discussions.microsoft.com> wrote in message
news:EE1329EC-C527-4269-BC50-3D8F2D1D27FC(a)microsoft.com...
> Hi All,
>
> How can I extract First letter from first name, remove the space between
> first and last name and keep the last name?
>
> For eg:
> I have a list of Names in this format:
> John Doe
> Will Smith
>
> and I want them like this:
> jdoe
> wsmith
>
> Thanks in advance

 | 
Pages: 1
Prev: Three questions about fields in worksheets
Next: Create menu with Workbook_open : error