identifying single letters and strings of letters in a cell

[bradstone]

If I type:

"1+A*2-B"

into a cell, I want to have a series of cells beneath that will hold each separate letter (or string, that would be helpful)

so in this case, i want:

1+A*2-B

A

B

<tbody>
</tbody>

Prefferably, it would also work for groups of letters, and also group them together:

100*fuel+2/tyres

FUEL

TYRES

<tbody>
</tbody>

Preferably no VBA as I want to convert the sheet into another code language.

 

[JLGWhiz]

Preferably no <ACRONYM title="visual basic for applications">VBA</ACRONYM> as I want to convert the sheet into another code language.

I don't believe VBA would impair the conversion of data on the worksheet to some othe code language. But I am sure someone can provide formulas to do what you want.

 

[bradstone]

I'm not sure, i was hoping to use appsgeyser.

I think you're right, it probably won't hurt...

Can a kindly patron help with with a script?

 

[Rick Rothstein]

I am not sure what "appsgeyser" is, but this is how I would do it using VBA in Excel. Select a cell with the text you want to split down the column and run this macro...

Sub SplitLettersAndWordsInSelectedCell()
  Dim X As Long, Text As String, Words() As String
  Text = ActiveCell.Text
  For X = 1 To Len(Text)
    If Mid(Text, X, 1) Like "[!A-Za-z]" Then Mid(Text, X) = " "
  Next
  Words = Split(Application.Trim(Text))
  ActiveCell.Offset(1).Resize(1 + UBound(Words)) = Application.Transpose(Words)
End Sub

Note: I cannot think of any way to do what you want using formulas only.

 

[JLGWhiz]

Another, but similar approach.
Since no range is specified to be used for typing data in, it is assumed that the macro should accomodate any entry made anywhere on the worksheet. The following is event code for the worksheet change event. To install the code, right click the name tab on the sheet where the entries will be made, then click 'View Code' in the pop up menu. Copy the code from this thread and paste it into the code window that appears. Close the code window and save the workbook as a macro enabled workbook. The code will now run any time a change is made to the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
    Dim x As Long
    Dim ch As String
    For x = 1 To Len(Target.Value)
        ch = Mid(Target.Value, x, 1)
        If ch Like "[a-zA-Z]" Then
            Ext = Ext & ch
        Else
            If Target.Offset(1, 0) = "" And Len(Ext) > 0 Then
            Target.Offset(1, 0) = Ext
            Ext = ""
            End If
        End If
    Next x
    Target.Offset(2, 0) = Ext
Application.EnableEvents = True
End Sub