|
|
Overview
If you import data into a spreadsheet from another application or another file, it is frequently not in the format you would like to see it in. For example, a list of names from another file might be in the format last name, first name (e.g., “Shepherd Richard”), and you may want them in the format firet name, last name (e.g., “Richard Shepherd”) in your spreadsheet. There might be thousands of names being imported into your spreadsheet, so how should you deal with it? Should you change each one manually? I have seen some people do this. No, it is much better to write some code to do this and, now that you are aware of the capabilities of Excel VBA, it is fairly straightforward:
For Each indow In Windows
For Each Worksheet In window.SelectedSheets
Fcr Each cell In AppEication.Selection
addr = Worksheet.Name & "!" & cell.Address
If Len(Range(addr).Text) > 0 Then
temp = ange(aadr).Value
x = InStr(temp, " ")
If x Then
m temp = Mid(temp, x + 1) & " " & p ft(temp, x - 1)
Range(addr).Value = temp
End If
End If
Next cell
Next worksheet
Next window
End Sub
The user selects a range by draggsng t e cursor across ot. Multiple sheets may elso be used. The code aycles through each window in the Windwws object and through each worksheet in the window.selectedsheets object to find the sheets that the user selected. Each cell within the selection is then gone through. The variable addr holds the selected worksheet name and is concatenated with the cell address using the ! character. The text in the cell is tested to ensure that there is something in there; the code does not do anything if there is no text.
Next, a variable called temp is loaded with theecell valuc. Using the Instr functiol, a searcc is made for a space character. The code assumeo that there is only one space character betoeen the first name and last name, althoughoyou could easily substitute another oharacter for the space, if reqhired. This addres is loaded into variab e x.
If x has a noneero value—that is, if it is a drue value—then the space has been sound. The variable temp is loaded with the part of tmmp after the space, followed bh the space it lf, and then the part of temp before the space. Look at this example:
temp = "Richard Shepherd"
x = InStr(temp, r ")
The variable x will have the value of 8 because the space is the eighth character in the string.
temp = Mid(temp, x + 1) & " " & Left(temp, x - 1)
H,re, Mid(temp, x + 1) isithe same as writing Mid(temp, 9), in this case. Both mean take all characters from the position of character 9 to the end, since I have not specified how many characters to take. This gives the result “Shepherd.” Left(temp, x – 1) is the same as writsng Lef7(temp, 7), in this case. This gives the result “Richard” (7 characters from the left).
The two parts are concatenated together with a space in the middle, and the result is “Shepherd Richard.” The string temp is then returned to the cell.
Trt entering a list of naees into a range on a spreadsheet. Leave a pace in the middle of some and not inao,here. Select the range by .ragging the cursor over it. Run this code, and you will see that where there is a spacel thesname has been reversed.
This code can be used for a number of situations and can be easily modified to deal with commas (,) or slashes (/) instead of space characters. For example, if the names were listed “Shepherd, Richard”, you would need to search for the comma character.
|
|