Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim or Len help (I think)
In my worksheet I have two columns.
Column A contains (LAST name, FIRST name) Column B contains (an ID number.) I would like to change Column A to contain only the FIRST 4 letters of the last name I would like to change Column B to contain only the LAST 4 numbers of the ID number I have had some experience using Word VBA, but not that much in Excel. I do want to do this using VBA because there are other steps that I need to take also. I'd appreciate any help that I can get on this. Elaine |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim or Len help (I think)
Insert helper column b for For Column A an enter in B =Left(A1,4)
Insert helper column d for your original Col b (which is now c) an enter in D =Right(C1,4) HTh "Elaine" wrote in message ... In my worksheet I have two columns. Column A contains (LAST name, FIRST name) Column B contains (an ID number.) I would like to change Column A to contain only the FIRST 4 letters of the last name I would like to change Column B to contain only the LAST 4 numbers of the ID number I have had some experience using Word VBA, but not that much in Excel. I do want to do this using VBA because there are other steps that I need to take also. I'd appreciate any help that I can get on this. Elaine |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim or Len help (I think)
If you adjust this to your particular situation it will do the job.
Using an array will make it faster than looping through the cells. Sub test() Dim i As Long Dim NameIDArray() NameIDArray = Range(Cells(1), Cells(20, 2)) For i = 1 To 20 NameIDArray(i, 1) = Left(NameIDArray(i, 1), 4) NameIDArray(i, 2) = Right(NameIDArray(i, 2), 4) Next Range(Cells(1), Cells(20, 2)) = NameIDArray End Sub RBS "Elaine" wrote in message ... In my worksheet I have two columns. Column A contains (LAST name, FIRST name) Column B contains (an ID number.) I would like to change Column A to contain only the FIRST 4 letters of the last name I would like to change Column B to contain only the LAST 4 numbers of the ID number I have had some experience using Word VBA, but not that much in Excel. I do want to do this using VBA because there are other steps that I need to take also. I'd appreciate any help that I can get on this. Elaine |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim or Len help (I think)
Elaine,
My code takes into account that some names are less than 4 characters. Typically, these are Chinese names such as "Eng", "Woo", "Ong" etc. My code also lets you set the row number of the first cell in the range. Set the FirstRw constant to this row. I have it currently set to row 5. Const FirstRw As Integer = 5 Sub ShortenData() Dim Rng As Range Dim i As Long, LastRw As Long Dim Pos As Integer Dim LastNm As String LastRw = Range("A" & Rows.Count).End(xlUp).Row Set Rng = Range("A" & FirstRw & ":B" & LastRw) For i = 1 To LastRw - FirstRw + 1 Pos = InStr(Rng(i, 1), ",") If Pos 0 Then LastNm = Left(Rng(i, 1), Pos - 1) Rng(i, 1) = Left(LastNm, Application.Min(Len(LastNm), 4)) End If Next For i = 1 To LastRw - FirstRw + 1 Rng(i, 2) = Right(Rng(i, 2), 4) Next End Sub Regards, Greg -----Original Message----- In my worksheet I have two columns. Column A contains (LAST name, FIRST name) Column B contains (an ID number.) I would like to change Column A to contain only the FIRST 4 letters of the last name I would like to change Column B to contain only the LAST 4 numbers of the ID number I have had some experience using Word VBA, but not that much in Excel. I do want to do this using VBA because there are other steps that I need to take also. I'd appreciate any help that I can get on this. Elaine . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim or Len help (I think)
A slightly shortened version:
Const FirstRw As Integer = 5 Sub ShortenData() Dim Rng As Range Dim i As Long, LastRw As Long Dim Pos As Integer Dim LastNm As String LastRw = Range("A" & Rows.Count).End(xlUp).Row Set Rng = Range("A" & FirstRw & ":B" & LastRw) Rng.Select For i = 1 To Rng.Count / 2 Pos = InStr(Rng(i, 1), ",") If Pos 0 Then LastNm = Left(Rng(i, 1), Pos - 1) Rng(i, 1) = Left(LastNm, Application.Min(Len(LastNm), 4)) End If Rng(i, 2) = Right(Rng(i, 2), 4) Next End Sub Regards, Greg -----Original Message----- Elaine, My code takes into account that some names are less than 4 characters. Typically, these are Chinese names such as "Eng", "Woo", "Ong" etc. My code also lets you set the row number of the first cell in the range. Set the FirstRw constant to this row. I have it currently set to row 5. Const FirstRw As Integer = 5 Sub ShortenData() Dim Rng As Range Dim i As Long, LastRw As Long Dim Pos As Integer Dim LastNm As String LastRw = Range("A" & Rows.Count).End(xlUp).Row Set Rng = Range("A" & FirstRw & ":B" & LastRw) For i = 1 To LastRw - FirstRw + 1 Pos = InStr(Rng(i, 1), ",") If Pos 0 Then LastNm = Left(Rng(i, 1), Pos - 1) Rng(i, 1) = Left(LastNm, Application.Min(Len(LastNm), 4)) End If Next For i = 1 To LastRw - FirstRw + 1 Rng(i, 2) = Right(Rng(i, 2), 4) Next End Sub Regards, Greg -----Original Message----- In my worksheet I have two columns. Column A contains (LAST name, FIRST name) Column B contains (an ID number.) I would like to change Column A to contain only the FIRST 4 letters of the last name I would like to change Column B to contain only the LAST 4 numbers of the ID number I have had some experience using Word VBA, but not that much in Excel. I do want to do this using VBA because there are other steps that I need to take also. I'd appreciate any help that I can get on this. Elaine . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim or Len help (I think)
Thank you so much. That was exactly what I was looking for.
Elaine "RB Smissaert" wrote in message ... If you adjust this to your particular situation it will do the job. Using an array will make it faster than looping through the cells. Sub test() Dim i As Long Dim NameIDArray() NameIDArray = Range(Cells(1), Cells(20, 2)) For i = 1 To 20 NameIDArray(i, 1) = Left(NameIDArray(i, 1), 4) NameIDArray(i, 2) = Right(NameIDArray(i, 2), 4) Next Range(Cells(1), Cells(20, 2)) = NameIDArray End Sub RBS "Elaine" wrote in message ... In my worksheet I have two columns. Column A contains (LAST name, FIRST name) Column B contains (an ID number.) I would like to change Column A to contain only the FIRST 4 letters of the last name I would like to change Column B to contain only the LAST 4 numbers of the ID number I have had some experience using Word VBA, but not that much in Excel. I do want to do this using VBA because there are other steps that I need to take also. I'd appreciate any help that I can get on this. Elaine |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trim | Excel Worksheet Functions | |||
=TRIM | Excel Discussion (Misc queries) | |||
Trim | Excel Discussion (Misc queries) | |||
trim | Excel Worksheet Functions | |||
Trim like worksheet Trim | Excel Programming |