G'day everyone

I'm trying to process some fairly complex Excel files in other editors (i.e. editors that simply extract the text from the Excel file) and it would be great if I could mark every cell with its reference. This would enable me to know which piece of text is in which cell, even if I'm not looking at it inside Excel.

So, is there a simple or quick way to add the cell reference of each cell to the start of that cell's content?

For example, if cell B5 has the content "The cat sat on the mat", then I would want cell B5's content to be updated to "[B5]The cat sat on the mat".

I understand that merged cells may pose difficulties but I don't care if merged cells' references are somewhat imprecise as long as I can figure out more or less where the cell is (e.g. as long as I know its row reference, or its column reference, or the reference of the top-left cell in the merged cell). Anyway the text that I'm concerned about generally don't occur in merged cells, so even if merged cells can't get references, it would still be okay.

I suppose this would have to be done using a macro -- I'm not afraid of adding macros to Excel although I'm not really a macro author (when I create macros in MS Word, for example, I do it mostly by recording).

Do you know of a program or tool or macro that would do what I specified above, i.e. to add each cell's cell reference to the start (or end) of the cell?

Thanks
Samuel (leuce)

Try the following code.

You need to select a range (you may select a non contiguous range as well).

The macro will put the cell's address and will concatenate the cell's content.

This works well with the Merged cells (top left corner's range address is used).

It ignores any blank cell in your selection.

Let me know if you face any issues.

To get this working. Put this code into a standard VBA module. Make a selection and run the macro (Alt + F8).

Option Explicit

Sub AddTitle()
Dim Temp As Variant
Dim cell As Range
If TypeName(Selection) < "Range" Then
MsgBox "Select the range to work with.", vbCritical
Exit Sub
End If

For Each cell In Selection.Cells
If Not cell.Value = "" Then
Temp = " " & cell.Value
cell.Value = cell.Address(False, False, xlA1) & Temp
End If
Next cell
End Sub


Thank You,
Prashant

Thanks, your code works very well. I'm impressed by your forethought of telling the user that nothing is selected when nothing is selected.

I have realised in the mean time that such a macro needs at least two extra checks, namely (a) if the cell contains a formula or (b) if the cell is hidden, it should not add the cell reference.

==

I tweaked it bit, and here is what I came up with:

Sub AddTitle()
Dim Temp As Variant
Dim cell As Range
If TypeName(Selection) < "Range" Then
MsgBox "Select the range to work with.", vbCritical
Exit Sub
End If

For Each cell In Selection.Cells
If Not cell.Rows.Hidden Then
If Not cell.Columns.Hidden Then
If Not cell.HasFormula Then

If Not cell.Value = "" Then
Temp = cell.Value
cell.Value = "{{" & ActiveSheet.Name & "}}[[" & cell.Address(False, False, xlA1) & "]]" & Temp
End If

End If
End If
End If
Next cell
End Sub

==

Thanks again
Samuel (leuce)

Hi Samuel,

Appreciate your feedback.

Glad it worked for you. I should have been a bit more thoughtful and should have foreseen these scenarios for you. Nevertheless, here is the code that checks for the additional checks. Just replace the line with IF condition with the following code.
If Not (cell.Value = "" Or cell.HasFormula Or cell.EntireColumn.Hidden Or cell.EntireRow.Hidden) Then

It would check for the additional checks and should work fine.

Let me know if you find any issues with this.

Thank You,
Prashant.

Hi Samuel,

Great to know it's working for you.
Please let me know for any other Excel or VBA related issue you face in future.

Thank You,
Prashant

Actually, if you could help with one more thing, I would be quite grateful.

There is an unintended side-effect to your macro which turned out to be more useful than the original purpose of the macro: the macro resets the sequence of the cells in the XSLX file's source code, in a manner of speaking. Why is this useful? Let me explain:

I'm a translator, and Excel files are often translated in separate programs that extract the text from the Excel file and merge the translated text back into the Excel file. There are expensive translation programs that parse Excel files intelligently, but some translators prefer free or opensource programs, which unfortunately extract the Excel cells blindly. Now, a little-known fact about XLSX files is that the cells stored inside the actual file are not in the same sequence as they appear on your screen, but in the order that they were last edited. So if an Excel file has been edited quite heavily before it is sent to the translator, the translator's tool will display the file's cells in seemingly random order, making translation difficult.

Your macro edits the cells in a logical order, which means that it resorts the cells inside the XLSX file back to their original sequence... almost. Since the macro works from the top to the bottom, the macro actually sorts the cells in reverse order (so that the cell at the bottom of the Excel sheet is stored near the top of the source code of the XLSX file, etc).

So, one very useful application for colleagues of mine using these opensource tools is to use one macro to add a single letter to the start of each cell and then use a second macro to remove a single letter from each cell (using "Left").

Would you be willing to write us two macros that does very much the same as your previous macros do, but "in reverse"?

* The one macro should process the selected cells one row at a time, and the other macro should process the selected cells one column at a time. These macros should edit each cell in the selection, starting with the last cell and finishing with the first cell. I'm not sure if this would be simple to do.

The macro you've written already is immensely useful nonetheless (in its unintended side-effect).

Thanks
Samuel (leuce)

Hi Samuel,

Sorry I couldn't see your post for some time as I was not feeling well and was not logging into this site for some time. Is your issue now solved?

I read your post but not sure if this is relevant to the answer I provided you for your first problem (adding cell reference to cell content).

Can you provide a sample and explain on that spreadsheet on what you need. It would be easy for me to write the macro on the same spreadsheet and send it back to you.

Thanks,
Prashant