Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I break down text in letters using VBA
Hi,
I have a Excel sheet in which text is to be typed by the user. The tex must be broken down in single letters, which the program recognises. For example: User types: Fox Program: 1: Take first letter 2: Check what letter it is 3: Use letter in script (for example a if then statement) 4: Take next letter 5: Repeat steps 2 to 5 untill all text is analysed Can anyone tell me how to do this? The breaking down of the text, tha is..... Thanks beforehand, Berend Botj -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I break down text in letters using VBA
Hi Berend,
As an example try: Sub Tester() Dim sStr As String Dim i As Long sStr = "FOX" For i = 1 To Len(sStr) MsgBox Mid(sStr, i, 1) Next End Sub --- Regards, Norman "Berend Botje " wrote in message ... Hi, I have a Excel sheet in which text is to be typed by the user. The text must be broken down in single letters, which the program recognises. For example: User types: Fox Program: 1: Take first letter 2: Check what letter it is 3: Use letter in script (for example a if then statement) 4: Take next letter 5: Repeat steps 2 to 5 untill all text is analysed Can anyone tell me how to do this? The breaking down of the text, that is..... Thanks beforehand, Berend Botje --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I break down text in letters using VBA
Use the Mid() function to breakdown the text string. Use a Select construct
to evaluate the value and execute corresponding code. Troy Sub Test1() Dim ii As Integer Dim sText As String Dim sTemp As String sText = "fox" For ii = 1 To Len(sText) sTemp = Mid(sText, ii, 1) Select Case sTemp Case "f" MsgBox "letter = f" Case "o" MsgBox "letter = o" Case "x" MsgBox "letter = x" 'add additional cases. Case Else 'no-match special case. End Select Next ii End Sub "Berend Botje " wrote in message ... Hi, I have a Excel sheet in which text is to be typed by the user. The text must be broken down in single letters, which the program recognises. For example: User types: Fox Program: 1: Take first letter 2: Check what letter it is 3: Use letter in script (for example a if then statement) 4: Take next letter 5: Repeat steps 2 to 5 untill all text is analysed Can anyone tell me how to do this? The breaking down of the text, that is..... Thanks beforehand, Berend Botje --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I break down text in letters using VBA
Hi Berend,
This will trap the input and process each letter. You will need to fill out the action to be taken Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target For i = 1 To Len(.Value) Select Case LCase(Mid(.Value, i, 1)) Case "a": 'handle a Case "b": 'handle b Case "c": 'handle c 'etc. End Select End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Berend Botje " wrote in message ... Hi, I have a Excel sheet in which text is to be typed by the user. The text must be broken down in single letters, which the program recognises. For example: User types: Fox Program: 1: Take first letter 2: Check what letter it is 3: Use letter in script (for example a if then statement) 4: Take next letter 5: Repeat steps 2 to 5 untill all text is analysed Can anyone tell me how to do this? The breaking down of the text, that is..... Thanks beforehand, Berend Botje --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I break down text in letters using VBA
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I break down text in letters using VBA
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I break down text in letters using VBA
According to the times on the postings, Bob posted his two minutes before
you said it was working. -- Regards, Tom Ogilvy "Berend Botje " wrote in message ... Like I said... it already works Thanks anyway. --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I break down text in letters using VBA
You're welcome, it was a real pleasure trying to help you!
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Berend Botje " wrote in message ... Like I said... it already works Thanks anyway. --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
break up cell text into two cells w text | Excel Discussion (Misc queries) | |||
break up cell text into two cells w text | Excel Discussion (Misc queries) | |||
break up cell text into two cells w text | Excel Discussion (Misc queries) | |||
Excel pie chart title words break to next line between letters? | Charts and Charting in Excel | |||
break up text | Excel Discussion (Misc queries) |