move data based on number of characters in a cell
Put this in B1 and copy down... =IF(LEN(A1)<4,A1,MID(A1,3,1))
Put this in C1 and copy down... =IF(LEN(A1)<4,A1,MID(A1,4,9))
--
Rick (MVP - Excel)
"aileen" wrote in message
...
This works, but it doesn't exclude data with less than 4 characters in
column
A. Is there a way to add that in to this code? Thanks everyone for the
speedy responses. I'm trying some of the other suggestions also.
"Rick Rothstein" wrote:
Assuming your data starts in Row 1...
Put this in B1 and copy down... =MID(A1,3,1)
Put this in C1 and copy down... =MID(A1,4,9)
The second formula assumes the numerical part of the entry will never be
more than 9 digits long... if it could be, just change the 9 to a number
bigger than the number of possible digits.
--
Rick (MVP - Excel)
"aileen" wrote in message
...
I have a column of data as such:
ESZ8
ESZ10
SPT
SPY
SPV
ESH9
SPC12
For the celss that have more than 3 characters I would like to separate
the
data into new columns as follows: ignore the first 2 characters then
place
the 3rd character in a new column and any remaining characters which
will
always be numbers in another column.
e.g
A B C
ESZ8 z 8
ESZ10 z 10
SPT
SPY
SPV
ESH9 h 9
SPC12 c 12
Is this at all possible? As usual, thank you for any help you can
give.
|