move data based on number of characters in a cell

  • Thread starter Thread starter aileen
  • Start date Start date
A

aileen

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.
 
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.
 
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
 
=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
 
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
 
and for the letter
=IF(AND(ISTEXT(MID(A1,3,1)), ISNUMBER(--MID(A1,4,1))),MID(A1,3,1),"")
 
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.
 
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.
 
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))
 
This worked perfectly...Thanks!

Don Guillett said:
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
(e-mail address removed)
Click to expand...
 
Back
Top