| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy middle digits from one cell into another
I need a formula to copy the middle digits from one cell to another. i.e.
132sk215zb from Cell A1 and transfer only the 2sk215 into Cell D1. Can anyone help? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy middle digits from one cell into another
If the original data is always the same length, and the data to be returned
is always in the same location within that data, try this: =LEFT(RIGHT(A1,8),6) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sharon R" <Sharon wrote in message ... I need a formula to copy the middle digits from one cell to another. i.e. 132sk215zb from Cell A1 and transfer only the 2sk215 into Cell D1. Can anyone help? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy middle digits from one cell into another
Try this more conventional way with a single function:
=MID(A1,3,6) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RagDyer" wrote in message ... If the original data is always the same length, and the data to be returned is always in the same location within that data, try this: =LEFT(RIGHT(A1,8),6) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sharon R" <Sharon wrote in message ... I need a formula to copy the middle digits from one cell to another. i.e. 132sk215zb from Cell A1 and transfer only the 2sk215 into Cell D1. Can anyone help? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy middle digits from one cell into another
Thank You. Its almost perfect. It copied the first numbers from the cell,
but I actually need it to pick up the middle 5 numbers. Can you help PLEASE! Thank You So Much! "RagDyer" wrote: If the original data is always the same length, and the data to be returned is always in the same location within that data, try this: =LEFT(RIGHT(A1,8),6) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sharon R" <Sharon wrote in message ... I need a formula to copy the middle digits from one cell to another. i.e. 132sk215zb from Cell A1 and transfer only the 2sk215 into Cell D1. Can anyone help? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy middle digits from one cell into another
Hi Sharon
Are you using the 2nd formula RD submitted, 'cause it works fine. =MID (A1,3,6).......remember to substitute A1 for whatever location you need !! HTH Michael M "Sharon R" wrote: Thank You. Its almost perfect. It copied the first numbers from the cell, but I actually need it to pick up the middle 5 numbers. Can you help PLEASE! Thank You So Much! "RagDyer" wrote: If the original data is always the same length, and the data to be returned is always in the same location within that data, try this: =LEFT(RIGHT(A1,8),6) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sharon R" <Sharon wrote in message ... I need a formula to copy the middle digits from one cell to another. i.e. 132sk215zb from Cell A1 and transfer only the 2sk215 into Cell D1. Can anyone help? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy middle digits from one cell into another
Or =MID(A1,((LEN(A3)/2)-1),5) if the length of the characters varies.
Tony On Jan 4, 8:08 am, Sharon R <Sharon wrote: I need a formula to copy the middle digits from one cell to another. i.e. 132sk215zb from Cell A1 and transfer only the 2sk215 into Cell D1. Can anyone help? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy middle digits from one cell into another
If you are now saying you want the middle 5 digits (though your original
example showed an output of 6 digits), what do you want if the number of digits in A1 is even? Try =MID(A1,(LEN(A1)-5)/2,5) or =MID(A1,ROUNDUP((LEN(A1)-5)/2,0),5) depending on which way round you want to treat even numbers. -- David Biddulph "Sharon R" wrote in message ... Thank You. Its almost perfect. It copied the first numbers from the cell, but I actually need it to pick up the middle 5 numbers. Can you help PLEASE! Thank You So Much! "RagDyer" wrote: If the original data is always the same length, and the data to be returned is always in the same location within that data, try this: =LEFT(RIGHT(A1,8),6) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sharon R" <Sharon wrote in message ... I need a formula to copy the middle digits from one cell to another. i.e. 132sk215zb from Cell A1 and transfer only the 2sk215 into Cell D1. Can anyone help? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy middle digits from one cell into another
Sorry, that should have been:
=MID(A1,1+(LEN(A1)-5)/2,5) or =MID(A1,ROUNDUP(1+(LEN(A1)-5)/2,0),5) or more simply =MID(A1,(LEN(A1)-3)/2,5) or =MID(A1,ROUNDUP((LEN(A1)-3)/2,0),5) -- David Biddulph "David Biddulph" wrote in message ... If you are now saying you want the middle 5 digits (though your original example showed an output of 6 digits), what do you want if the number of digits in A1 is even? Try =MID(A1,(LEN(A1)-5)/2,5) or =MID(A1,ROUNDUP((LEN(A1)-5)/2,0),5) depending on which way round you want to treat even numbers. -- David Biddulph "Sharon R" wrote in message ... Thank You. Its almost perfect. It copied the first numbers from the cell, but I actually need it to pick up the middle 5 numbers. Can you help PLEASE! Thank You So Much! "RagDyer" wrote: If the original data is always the same length, and the data to be returned is always in the same location within that data, try this: =LEFT(RIGHT(A1,8),6) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sharon R" <Sharon wrote in message ... I need a formula to copy the middle digits from one cell to another. i.e. 132sk215zb from Cell A1 and transfer only the 2sk215 into Cell D1. Can anyone help? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct issues | Excel Worksheet Functions | |||
| Copy Cell to Word ? | Excel Discussion (Misc queries) | |||
| need to Copy or Move to active cell from specified range | Excel Discussion (Misc queries) | |||
| How to Copy the value of a cell to any given cell | Excel Discussion (Misc queries) | |||
| hpw do I logic test a cell then copy the row to diff. SS | Excel Worksheet Functions | |||
Linear Mode
