Using VBA to Split Words Into Letters

[ransomedbyfire]

I am trying to write a macro that will separate the last letter from a text string. So, "TestA", for example, could be split into "Test" and "A". How can I do this since there aren't any delimiters?

 

[bertie]

Try this, Debug.Print outputs to the Immediate Window, click View => Immediate Window

[color=darkblue]Sub[/color] SplitText()
   [color=darkblue]Dim[/color] InputText [color=darkblue]As[/color] [color=darkblue]String[/color]
   [color=darkblue]Dim[/color] StringA [color=darkblue]As[/color] [color=darkblue]String[/color]
   [color=darkblue]Dim[/color] StringB [color=darkblue]As[/color] String
   
   InputText = "TestA"
   StringA = Left(InputText, Len(InputText) - 1)
   StringB = Right(InputText, 1)
   
   Debug.Print StringA
   Debug.Print StringB
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[hiker95]

ransomedbyfire,


Sample data before the macro:

Excel Workbook
	A	B
1	TestA
2
Sheet1

After the macro:

Excel Workbook
	A	B
1	Test	A
2
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SplitA()
' hiker95, 05/07/2011
' http://www.mrexcel.com/forum/showthread.php?t=548552
Dim c As Range
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  c.Offset(, 1) = Right(c, 1)
  c = Left(c, Len(c) - 1)
Next c
End Sub

Then run the SplitA macro.