Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 128
Default Copying first three characters of text in a cell

I need to concatenate text from two cells but only the first three characters
from one of the cells. Other than subtracting the last 5 characters, is there
a simpler way?

A1 is "ABC1"
B1 is "XYZ RSTU"
I need to display in cell C1 this ABC1-XYZ
I've used this formula =A1&"-"&LEFT(B1, LEN(B1)-5)

Thanks
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Copying first three characters of text in a cell

Yes, there is a simpler way to achieve this without subtracting the last 5 characters. You can use the LEFT function to extract the first three characters from cell A1 and then concatenate it with a hyphen and the first three words from cell B1. Here's how you can do it:
  1. In cell C1, type the following formula:
    Formula:
    =LEFT(A1,3)&"-"&LEFT(B1,FIND(" ",B1)-1
  2. Press Enter to apply the formula.

The LEFT function extracts the first three characters from cell A1 and the FIND function finds the position of the first space in cell B1. The LEFT function then extracts the characters from the beginning of cell B1 up to the position of the first space minus one (to exclude the space). Finally, the two results are concatenated with a hyphen using the ampersand (&) operator.

This formula should give you the desired result of "ABC1-XYZ" in cell C1.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 69
Default Copying first three characters of text in a cell


Hi Kris,

Use following formula,

=A1&"-"&MID(B1,1,3)

H S Shastri



================================================== =
"Kris" wrote:

I need to concatenate text from two cells but only the first three characters
from one of the cells. Other than subtracting the last 5 characters, is there
a simpler way?

A1 is "ABC1"
B1 is "XYZ RSTU"
I need to display in cell C1 this ABC1-XYZ
I've used this formula =A1&"-"&LEFT(B1, LEN(B1)-5)

Thanks

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Copying first three characters of text in a cell

Why not just =A1&"-"&LEFT(B1, 3) ?

Hint:
LEFT is a standard Excel function, so if you don't know what it's doing,
look it up in Excel help. The same applies to LEN.
If you haven't used Excel help yet, I would heartily recommend it.
--
David Biddulph


"Kris" wrote in message
...
I need to concatenate text from two cells but only the first three
characters
from one of the cells. Other than subtracting the last 5 characters, is
there
a simpler way?

A1 is "ABC1"
B1 is "XYZ RSTU"
I need to display in cell C1 this ABC1-XYZ
I've used this formula =A1&"-"&LEFT(B1, LEN(B1)-5)

Thanks



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Copying first three characters of text in a cell

Is there any advantage in using MID instead of LEFT?
--
David Biddulph

"HARSHAWARDHAN. S .SHASTRI"
wrote in message
...
Hi Kris,

Use following formula,

=A1&"-"&MID(B1,1,3)

H S Shastri


================================================== =
"Kris" wrote:

I need to concatenate text from two cells but only the first three
characters
from one of the cells. Other than subtracting the last 5 characters, is
there
a simpler way?

A1 is "ABC1"
B1 is "XYZ RSTU"
I need to display in cell C1 this ABC1-XYZ
I've used this formula =A1&"-"&LEFT(B1, LEN(B1)-5)

Thanks



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
Count Text Characters in cell K[_2_] Excel Discussion (Misc queries) 3 January 10th 09 09:16 PM
removing text characters from a cell volleygods Excel Worksheet Functions 12 December 24th 08 06:07 PM
Text Field in a cell changes characters brewster56 Excel Discussion (Misc queries) 1 November 13th 08 11:24 PM
Why has my cell text been replaced by # characters? sggp Excel Discussion (Misc queries) 1 August 9th 05 09:31 PM
how do I highlite text within a cell (specific characters) tim Excel Discussion (Misc queries) 1 May 20th 05 05:23 AM


All times are GMT +1. The time now is 07:51 PM.

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"