Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mat mat is offline
external usenet poster
 
Posts: 37
Default Copy first few letters from a cell

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 897
Default Copy first few letters from a cell

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

On Jan 17, 4:44*pm, Mat wrote:
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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,942
Default Copy first few letters from a cell

hi
how about a formula.
=left(A1,5)
copy down. if you want hard letters, copy formula column and paste as values.

Regards
FSt1

"Mat" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Copy first few letters from a cell

=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" wrote in message
...
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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default Copy first few letters from a cell

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

"Mat" wrote in message
...
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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,202
Default Copy first few letters from a cell

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" wrote in message
...
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

"Mat" wrote in message
...
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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mat mat is offline
external usenet poster
 
Posts: 37
Default Copy first few letters from a cell

Dear Fst1,

Thanks. That was simple.

Regards

Mat

"FSt1" wrote:

hi
how about a formula.
=left(A1,5)
copy down. if you want hard letters, copy formula column and paste as values.

Regards
FSt1

"Mat" wrote:

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

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mat mat is offline
external usenet poster
 
Posts: 37
Default Copy first few letters from a cell

Thank you

"FSt1" wrote:

hi
how about a formula.
=left(A1,5)
copy down. if you want hard letters, copy formula column and paste as values.

Regards
FSt1

"Mat" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to change small letters to capital letters HOW TO CHANGE Excel Discussion (Misc queries) 4 May 30th 07 01:12 AM
How do change a column of data in capitol letters to small letters Barb P. Excel Discussion (Misc queries) 6 November 15th 06 07:17 PM
change lower letters to upper letters Winnie Excel Discussion (Misc queries) 2 September 15th 06 04:58 AM
letters cut off in cell Brian Excel Worksheet Functions 1 August 19th 06 09:15 PM
Copy the first two or three letters of a cell to another one Jerry Excel Discussion (Misc queries) 4 February 22nd 06 07:29 AM


All times are GMT +1. The time now is 11:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"