identifying single letters and strings of letters in a cell

bradstone

New Member
Joined
Sep 13, 2014
Messages
4
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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Sep 13, 2014
Messages
4
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
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...
Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top