Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Copy first few letters from a cell

18,548 views
Skip to first unread message

Mat

unread,
Jan 17, 2008, 4:44:02 PM1/17/08
to
Dear mate,

I have 6000 client names and some are duplicates. I need to copy just the
first 5 letters of each client from the cell and place in the next cell. This
will help me to sum up all the ABCDE revenue as they are all same client with
different names with the first 5 letters are common.
Hope I was able to explain my issue,

For example

ABCDE Ltd
ABCDE co.
ABCDE inc
ABCDE ltee

Regards
Mat

JP

unread,
Jan 17, 2008, 4:56:01 PM1/17/08
to
Let's say the first client name was in A1, put this formula in B1 and
fill down as needed.

=LEFT(A1,5)

Don't forget to Copy/Paste Values to get the data hardcoded into your
worksheet.

HTH,
JP

FSt1

unread,
Jan 17, 2008, 4:56:02 PM1/17/08
to
hi
how about a formula.
=left(A1,5)
copy down. if you want hard letters, copy formula column and paste as values.

Regards
FSt1

T. Valko

unread,
Jan 17, 2008, 4:57:55 PM1/17/08
to
=LEFT(A1,5)

Will return the first 5 characters from cell A1.

However, if you want to get a sum based on the first 5 characters you really
don't need to use a helper column.

> ABCDE Ltd...50
> ABCDE co....10
> ABCDE inc....20
> ABCDE ltee...10

Try one of these:

=SUMIF(A1:A10,"*abcde*",B1:B10)

Or

=SUMPRODUCT(--(LEFT(A1:A10,5)="abcde"),B1:B10)


--
Biff
Microsoft Excel MVP


"Mat" <M...@discussions.microsoft.com> wrote in message
news:39B56BC1-2381-4D98...@microsoft.com...

Don Guillett

unread,
Jan 17, 2008, 5:04:09 PM1/17/08
to
No need to do that. Use SUMPRODUCT where col A is the co name and col B is
the numbers to be summed.


=sumproduct((left(a2:a22,5)="ABCDE")*B2:B22)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com


"Mat" <M...@discussions.microsoft.com> wrote in message
news:39B56BC1-2381-4D98...@microsoft.com...

Rick Rothstein (MVP - VB)

unread,
Jan 17, 2008, 5:13:54 PM1/17/08
to
You can also do it with a SUMIF function call...

=SUMIF(A1:A10,"ABCDE*",B1:B10)

(Mat... note the asterisk after the 5 character string you want to sum for)

Rick


"Don Guillett" <dguil...@austin.rr.com> wrote in message
news:eHtYnTVW...@TK2MSFTNGP05.phx.gbl...

Mat

unread,
Jan 18, 2008, 10:05:00 AM1/18/08
to
Dear Fst1,

Thanks. That was simple.

Regards

Mat

Mat

unread,
Jan 18, 2008, 10:44:01 AM1/18/08
to
Thank you

Don Guillett

unread,
Jan 18, 2008, 12:45:40 PM1/18/08
to
Rick, It appears that the OP wants to do it the hard way.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com

"Rick Rothstein (MVP - VB)" <rickNOS...@NOSPAMcomcast.net> wrote in
message news:OMGh1YVW...@TK2MSFTNGP03.phx.gbl...

Rick Rothstein (MVP - VB)

unread,
Jan 18, 2008, 1:07:02 PM1/18/08
to
Yes, it does seem so.

Rick


"Don Guillett" <dguil...@austin.rr.com> wrote in message

news:ukEQynfW...@TK2MSFTNGP03.phx.gbl...

0 new messages