| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
move data based on number of characters in a cell
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
move data based on number of characters in a cell
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
move data based on number of characters in a cell
for the data given, this should do it.
Sub breakitup() For Each c In Range("c2:c10") If Len(c) 3 Then c.Offset(, 1) = Mid(c, 3, 1) c.Offset(, 2) = Mid(c, 4, 2) End If Next c -- Don Guillett Microsoft MVP Excel SalesAid Software "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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
move data based on number of characters in a cell
=IF(AND(ISTEXT(MID(A1,3,1)), ISNUMBER(--MID(A1,4,1))),--MID(A1,4,255),"")
Assuming always AAANN or AAA Will not work with AAAAN Please tell us your exact requirements best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
move data based on number of characters in a cell
Select the column, then click on Data - Text to columns. Select "fixed
width". Set your first width to 3 characters, the next width to one character, and ignore the rest. HTH Otto "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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
move data based on number of characters in a cell
and for the letter
=IF(AND(ISTEXT(MID(A1,3,1)), ISNUMBER(--MID(A1,4,1))),MID(A1,3,1),"") -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bernard Liengme" wrote in message ... =IF(AND(ISTEXT(MID(A1,3,1)), ISNUMBER(--MID(A1,4,1))),--MID(A1,4,255),"") Assuming always AAANN or AAA Will not work with AAAAN Please tell us your exact requirements best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
move data based on number of characters in a cell
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
move data based on number of characters in a cell
Yes, the data will always be AAANN. This code is ignoring less than 4
characters which is good, but it's only giving the number field. For some reason, the text isn't showing up. It would actually be fine for the data to appear in one cell. e.g. with ESV8, I should see V8. Any suggestions? And thanks for the quick response. "Bernard Liengme" wrote: =IF(AND(ISTEXT(MID(A1,3,1)), ISNUMBER(--MID(A1,4,1))),--MID(A1,4,255),"") Assuming always AAANN or AAA Will not work with AAAAN Please tell us your exact requirements best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
move data based on number of characters in a cell
This worked perfectly...Thanks!
"Don Guillett" wrote: for the data given, this should do it. Sub breakitup() For Each c In Range("c2:c10") If Len(c) 3 Then c.Offset(, 1) = Mid(c, 3, 1) c.Offset(, 2) = Mid(c, 4, 2) End If Next c -- Don Guillett Microsoft MVP Excel SalesAid Software "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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return value based on number of characters in cell / field | Excel Worksheet Functions | |||
| Deleting rows based on number of characters | Excel Discussion (Misc queries) | |||
| moving cells to another column based on number of characters | Excel Programming | |||
| Split field based on number of characters and space | Excel Worksheet Functions | |||
| Split field based on number of characters and space | Excel Programming | |||
Linear Mode
