How to add cell's reference to cell's content

[leuce]

G'day everyone

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

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

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

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

I suppose this would have to be done using a macro -- I'm not afraid o
adding macros to Excel although I'm not really a macro author (when
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 specifie
above, i.e. to add each cell's cell reference to the start (or end) o
the cell?

Thanks
Samuel (leuce

 

[leuce]

Try the following code. ...

Thanks, your code works very well. I'm impressed by your forethought o
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 tw
extra checks, namely (a) if the cell contains a formula or (b) if th
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

 

[jack_n_bub]

Thanks, your code works very well. I'm impressed by your forethought o
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 tw
extra checks, namely (a) if the cell contains a formula or (b) if th
cell is hidden, it should not add the cell reference. Do you min
adding that for me to the macro? If not, I'm sure I'll be able t
figure it out with googling.

Thanks again
Samuel (leuce)

Hi Samuel,

Appreciate your feedback.

Glad it worked for you. I should have been a bit more thoughtful an
should have foreseen these scenarios for you. Nevertheless, here is th
code that checks for the additional checks. Just replace the line wit
IF condition with the following code.
If Not (cell.Value = "" Or cell.HasFormula Or cell.EntireColumn.Hidde
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

 

[jack_n_bub]

Thanks, your code works very well. I'm impressed by your forethought o
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 tw
extra checks, namely (a) if the cell contains a formula or (b) if th
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,

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

Thank You,
Prashan

 

[leuce]

Please let me know for any other Excel or VBA related issue you face i
future.

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

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

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

Your macro edits the cells in a logical order, which means that i
resorts the cells inside the XLSX file back to their origina
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 a
the bottom of the Excel sheet is stored near the top of the source cod
of the XLSX file, etc).

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

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

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

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

Thanks
Samuel (leuce

 

[jack_n_bub]

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

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

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

Your macro edits the cells in a logical order, which means that i
resorts the cells inside the XLSX file back to their origina
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 a
the bottom of the Excel sheet is stored near the top of the source cod
of the XLSX file, etc).

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

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

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

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

Thanks
Samuel (leuce)

Hi Samuel,

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

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

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

Thanks,
Prashan