Please help me out of a Microsoft Excel morass

January 27, 2011 8:22 AM

If I don't figure out a solution, I may be doomed to hours upon hours of copying and pasting in Excel. My brain is crying out in pain for a solution, but I've never encountered this issue before. I need to transform sheet names into a variable on the sheet they represent. I'll try to explain this better inside.

I am trying to consolidate thousands of addresses into one spreadsheet so that I can do a statistical merge and eliminate duplicate addresses. The data that received looks like this:

-There are 27 different excel files, one for each letter of the alphabet and one for streets that start with a numeral (1st St., 2nd Ave., etc).
-Within each spreadsheet, the columns include street number, City, etc., but NOT the street name. Instead, the street name is the name of the sheet where the rest of the data resides. So for example, the spreadsheet "A" might have 30 sheets within, all representing different street names, all containing addresses that are on the respective street.

If I could change this world, the street name would be just another column. Is there any way that I can make the name of the sheet (street name) into a value for the column street name?

I have 27 total spreadsheets, each with anywhere from 5-40 sheets, each containing addresses that I have been copying and pasting, then shifting around to manually enter the street name. I'm not above doing data entry, but I have a lot more to my job than this, so if there's a way to bypass this timesuck drudge pit, please share some wisdom.

Thanks so much.

posted by SpicyMustard to Computers & Internet (13 answers total)

Checking the Excel help for the CELL function should get you most of the way there.
posted by flabdablet at 8:32 AM on January 27, 2011

The formula,
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
Will return the filename. If you just do Cell("filename", A1) they you get the full path to the file as well, which you don't want.

For the worksheet it is a little more complicated. You will need to create a custom macro called GetSheetName.

Function GetSheetName()
GetSheetName = ActiveSheet.Name
End Function

Then you can use GetSheetName() as a function in a cell, the same way that you can use Excel built-in functions.
posted by atrazine at 8:44 AM on January 27, 2011 [2 favorites]

Specifically, putting =CELL("filename",A1) in a worksheet cell will get you the pathname and sheet name of that worksheet. You will need to do a bit of MID() and FIND() foolery to isolate the sheet name. I don't have Excel here unfortunately so I forget exactly what the format of the CELL() result is; there are square brackets in it somewhere if I remember right and I don't remember it being too hard to take apart.

That's the actual word "filename", by the way - don't substitute the name of your file.
posted by flabdablet at 8:47 AM on January 27, 2011

Actually, this will get the sheet name:

=MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

posted by Doofus Magoo at 8:48 AM on January 27, 2011

... and it doesn't matter what cells you reference (four locations) in that formula.
posted by Doofus Magoo at 8:49 AM on January 27, 2011

Another option for the sheet name:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))
posted by doctord at 8:50 AM on January 27, 2011

Response by poster: Thanks for the quick responses. I am trying it out now.
posted by SpicyMustard at 8:51 AM on January 27, 2011

On preview: Google has once again rendered my memory completely obsolete.
posted by flabdablet at 8:51 AM on January 27, 2011 [1 favorite]

That function atrazine posted will actually find you the worksheet name, not the filename. This macro will put a column called "Street Name" with the name of the worksheet in each row in column A:

Note: for this to work your current column A must be a continuous column of data -- any empty cells and this won't go to the bottom.

As is you'll have to go into each sheet and run it, but you can probably figure out how to make it step through a workbook with a little googling


Sub WorksheetName()
'
' WorksheetName Macro
'

'
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Street Name"
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=MID(CELL(""Filename"",R[-1]C),FIND(""]"",CELL(""Filename"",R[-1]C))+1,255)"
Range("B1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Range("A1").Select

End Sub

posted by brainmouse at 8:51 AM on January 27, 2011

Atrazine is correct, I just tried it. Because the problem is that each filename has a bunch of different worksheets corresponding to street names. So you WANT the worksheet name, not the filename.

Brainmouse's macro ought to work, if you replace the "MID(CELL..." stuff with your module name.

You could probably add the macro to each file, and then create another macro that steps through the worksheets and runs it on each worksheet.
posted by gjc at 9:18 AM on January 27, 2011

Here is a macro that will cycle through each sheet in a workbook and place the sheet names on cell A1. If you like, you can combine this with the copy down section of the macro posted by brainmouse.


Sub SheetNameRobot()

' SheetNameRobot Macro
' Places the sheet name in a new A column on cell A1 of each sheet

Dim sheet As Worksheet
Dim currentSheet As String

currentSheet = ActiveSheet.Name
Application.ScreenUpdating = False

For Each sheet In Application.Worksheets
  sheet.Activate
  Columns("A:A").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("A1").Select
  ActiveCell.FormulaR1C1 = ActiveSheet.Name
Next sheet

Worksheets(currentSheet).Activate
Range("A1").Select

End Sub

posted by thewildgreen at 5:30 PM on January 27, 2011

Check out Google Refine.

It's specifically designed to help fix unruly or inconsistent data formatting.
posted by j03 at 1:00 AM on January 28, 2011

Response by poster: Thank you everyone. This was incredibly helpful.
posted by SpicyMustard at 1:15 PM on January 31, 2011