Extract and Delete Characters Based on Condition

[madhuchelliah]

Hello all, i want to extract data in a selected range of cells based on condition. If the 9th character is an alphabet, then the rest of the characters(from 10th) in the cell to be copied to the adjacent cell. If it is not an alphabet then from 9th character in the cell to be copied to the adjacent cell. In both cases after copying, the copied data should be deleted from the selected range. Please see the example for better clarity. Example is just for reference. Thank you.

Book1
	I	J	K	L	M
14	Input			Output
15	12345678A002X			12345678A	002X
16	12345678910			12345678	910
Sheet1

 

[Fluff]

How about

+Fluff 1.xlsm
	I	J	K	L	M
14	Input			Output
15	12345678A002X			12345678A	002X
16	12345678910			12345678	910
Main

Cell Formulas
Range	Formula
L15:L16	L15	=LEFT(I15,8+ISERROR(MID(I15,9,1)+0))
M15:M16	M15	=MID(I15,9+ISERROR(MID(I15,9,1)+0),100)

 

[madhuchelliah]

How about

+Fluff 1.xlsm
	I	J	K	L	M
14	Input			Output
15	12345678A002X			12345678A	002X
16	12345678910			12345678	910
Main

Cell Formulas
Range	Formula
L15:L16	L15	=LEFT(I15,8+ISERROR(MID(I15,9,1)+0))
M15:M16	M15	=MID(I15,9+ISERROR(MID(I15,9,1)+0),100)

Hello Fluff, my apologies not to mention the requirement as VBA solution. BTW the formula works great. Thank you.

 

[Fluff]

What column is the data in & what is the first row?

 

[madhuchelliah]

What column is the data in & what is the first row?

It should be work for any selected range in any column. Adjacent cell should be the next column of selected one. Hope it clears.

 

[Fluff]

Ok, how about

Sub madhuchelliah()
   With Selection
      .Offset(, 1).Value = Evaluate(Replace("if({1},mid(@,9+iserror(mid(@,9,1)+0),100))", "@", .Address))
      .Value = Evaluate(Replace("if({1},left(@,8+iserror(mid(@,9,1)+0)))", "@", .Address))
   End With
End Sub

 

[madhuchelliah]

Ok, how about

Sub madhuchelliah()
   With Selection
      .Offset(, 1).Value = Evaluate(Replace("if({1},mid(@,9+iserror(mid(@,9,1)+0),100))", "@", .Address))
      .Value = Evaluate(Replace("if({1},left(@,8+iserror(mid(@,9,1)+0)))", "@", .Address))
   End With
End Sub

Hello Fluff, it is working as expected. Thanks for your time.

 

[Fluff]

You're welcome & thanks for the feedback.