Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find a comma in a string
A1 contains LastName, FirstName
If B1 = mid(A1,1,find(",",A1)-1) then B1 will be = to the LastName. How can I write a macro that will returen the LastName to whatever the ActiveCell is? Chuck L |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find a comma in a string
ActiveCell.Value = Mid(Range("A1"),1,Instr(Range("A1"),",")-1)
-- Regards, Tom Ogilvy "Chuck" wrote in message ... A1 contains LastName, FirstName If B1 = mid(A1,1,find(",",A1)-1) then B1 will be = to the LastName. How can I write a macro that will returen the LastName to whatever the ActiveCell is? Chuck L |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find a comma in a string
iPos = Instr(1,Range("A1").Value,",") If iPos 0 Then Activecell.Value = Right(Range("A1").Value, _ Len(Range("A1").Value) - iPos) End If -- HTH RP (remove nothere from the email address if mailing direct) "Chuck" wrote in message ... A1 contains LastName, FirstName If B1 = mid(A1,1,find(",",A1)-1) then B1 will be = to the LastName. How can I write a macro that will returen the LastName to whatever the ActiveCell is? Chuck L |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find a comma in a string
Think you would need LEFT for lastname.
Sub AA() iPos = InStr(1, Range("A1").Value, ",") If iPos 0 Then ActiveCell.Value = Left(Range("A1").Value, _ iPos - 1) End If End Sub -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... iPos = Instr(1,Range("A1").Value,",") If iPos 0 Then Activecell.Value = Right(Range("A1").Value, _ Len(Range("A1").Value) - iPos) End If -- HTH RP (remove nothere from the email address if mailing direct) "Chuck" wrote in message ... A1 contains LastName, FirstName If B1 = mid(A1,1,find(",",A1)-1) then B1 will be = to the LastName. How can I write a macro that will returen the LastName to whatever the ActiveCell is? Chuck L |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find a comma in a string
On Tue, 23 Aug 2005 21:44:11 +0100, "Bob Phillips"
wrote: iPos = Instr(1,Range("A1").Value,",") If iPos 0 Then Activecell.Value = Right(Range("A1").Value, _ Len(Range("A1").Value) - iPos) End If Slightly different approach. However, *Range* is the magic word. Thank you. Chuck |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find a comma in a string
On Tue, 23 Aug 2005 21:18:41 -0400, "Tom Ogilvy" wrote:
Think you would need LEFT for lastname. Sub AA() iPos = InStr(1, Range("A1").Value, ",") If iPos 0 Then ActiveCell.Value = Left(Range("A1").Value, _ iPos - 1) End If End Sub I have a number of cell I'm working with and all but this one require the *Mid* function. I just got carried away with *Mid*. However, I am going to change this one cell to use *Left*. It is just a cleaner function for this case. Thank you. Chuck -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find a comma in a string
On Tue, 23 Aug 2005 16:36:15 -0400, "Tom Ogilvy" wrote:
ActiveCell.Value = Mid(Range("A1"),1,Instr(Range("A1"),",")-1) *Range* is the magic word. I had the ActiveCell.Value part. Thank you. Chiuck |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find a comma in a string
On Tue, 23 Aug 2005 21:44:11 +0100, "Bob Phillips"
wrote: iPos = Instr(1,Range("A1").Value,",") If iPos 0 Then Activecell.Value = Right(Range("A1").Value, _ Len(Range("A1").Value) - iPos) End If A slightly different approach. Hwever, *Range* is still the magic word. Thank you. Chuck -- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find a comma in a string
On Tue, 23 Aug 2005 16:36:15 -0400, "Tom Ogilvy" wrote:
ActiveCell.Value = Mid(Range("A1"),1,Instr(Range("A1"),",")-1) *Range* is the magic word. Thank you Chuck -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text string separated by comma | Excel Discussion (Misc queries) | |||
test text string for a comma (,) | Excel Discussion (Misc queries) | |||
Macro to find text string in a column and paste data in another | Excel Discussion (Misc queries) | |||
find and remove a string of a cell value with comma as delimiter | Excel Discussion (Misc queries) | |||
Convert comma separated string to DropDown | Excel Programming |