Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function?
I am looking for a way to split the data in one cell into multiple values
that can each be referenced from another cell. For example, the value of my data cell is "001122". The first two digits represent one value, the second two digits represent the second value, and the last two digits represent the third value. Now I need to reference each value independent of the other two. So, I want to return the value of the first two digits only, or the second two digits only, or the last two digits only. Is there any way to accomplish what I'm after? Thank you, Kent |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function?
=VALUE(LEFT(A1,2))
=VALUE(MID(A1,3,2)) =VALUE(RIGHT(A1,2)) "Kent" wrote in message ... I am looking for a way to split the data in one cell into multiple values that can each be referenced from another cell. For example, the value of my data cell is "001122". The first two digits represent one value, the second two digits represent the second value, and the last two digits represent the third value. Now I need to reference each value independent of the other two. So, I want to return the value of the first two digits only, or the second two digits only, or the last two digits only. Is there any way to accomplish what I'm after? Thank you, Kent |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function?
The left hand part:
=LEFT(A1,2) The right hand part: =RIGHT(A1,2) The middle part: =MID(A1,3,2) with your data in A1. Hope this helps. Pete On Dec 17, 2:30 pm, Kent wrote: I am looking for a way to split the data in one cell into multiple values that can each be referenced from another cell. For example, the value of my data cell is "001122". The first two digits represent one value, the second two digits represent the second value, and the last two digits represent the third value. Now I need to reference each value independent of the other two. So, I want to return the value of the first two digits only, or the second two digits only, or the last two digits only. Is there any way to accomplish what I'm after? Thank you, Kent |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function?
You could select the cell(s) with the 6 digit; use the menu command Data |
Text to Columns ; specify Fixed with and then click between the digits on the dialog to get 12|34|56. Or you could use some math: Let A13 hold the number 123456 then =INT(A13/10000) yields 12, =INT((MOD(A13,10000))/100) yields 34 and =MOD(A13,100) yields 56. Or you could use =MID(A10,1,2) to extract 12 from 123456 in A10 or =MID(A10,3,2) to extract 34, etc Or =MID(A10,B10,2) where B10 is given values 1, 3 or 5 to extract the three parts you want. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Kent" wrote in message ... I am looking for a way to split the data in one cell into multiple values that can each be referenced from another cell. For example, the value of my data cell is "001122". The first two digits represent one value, the second two digits represent the second value, and the last two digits represent the third value. Now I need to reference each value independent of the other two. So, I want to return the value of the first two digits only, or the second two digits only, or the last two digits only. Is there any way to accomplish what I'm after? Thank you, Kent |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function?
You can split it with Data/ Text to Columns/ Fixed width
Another option is to use =LEFT(A2,2) assuming that the input is a text string, not a number formatted as 000000. [ If the latter, then try =LEFT(TEXT(A2,"000000"),2) ] =MID(A2,3,2) =RIGHT(A2,2) If you want the output strings to become numbers, put a double unary minus -- in front of the LEFT, MID, or RIGHT. -- David Biddulph "Kent" wrote in message ... I am looking for a way to split the data in one cell into multiple values that can each be referenced from another cell. For example, the value of my data cell is "001122". The first two digits represent one value, the second two digits represent the second value, and the last two digits represent the third value. Now I need to reference each value independent of the other two. So, I want to return the value of the first two digits only, or the second two digits only, or the last two digits only. Is there any way to accomplish what I'm after? Thank you, Kent |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function?
Thanks to all who responded to my query. I ended up using Stephan's
suggestion as it seemed the most direct. It worked like a charm! Sincerely, Kent "Kent" wrote: I am looking for a way to split the data in one cell into multiple values that can each be referenced from another cell. For example, the value of my data cell is "001122". The first two digits represent one value, the second two digits represent the second value, and the last two digits represent the third value. Now I need to reference each value independent of the other two. So, I want to return the value of the first two digits only, or the second two digits only, or the last two digits only. Is there any way to accomplish what I'm after? Thank you, Kent |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
Excel 2002: Auto Sum function not working in large Excel file | Excel Discussion (Misc queries) | |||
challenge! javascript function into excel function | Excel Worksheet Functions | |||
Excel Workday Function with another function | Excel Discussion (Misc queries) | |||
Can you nest a MID function within a IF function in Excel | Excel Worksheet Functions |