Sorry if this is really easy or not possible. I'm a dba with very little
Excel experience. I've been given an Excel spreadsheet to import into a
relational database there are some data consistency issues...

Anyway, what I'd like to do is insert an underscore at the front of the data
in each cell in a column - preferably in only those cells that have data. So
if the cell has ABCDEF in it it'd become _ABCDEF .

Any clues on how to accomplish this? Thanks.

Use a helper column:

Say your data is in column A. In B1 enter:

="_" & A1
and copy down. Then copy column B and paste/special/values back onto column
A.
--
Gary''s Student - gsnu200781


"Jon in Canby Or." wrote:

Sorry if this is really easy or not possible. I'm a dba with very little
Excel experience. I've been given an Excel spreadsheet to import into a
relational database there are some data consistency issues...

Anyway, what I'd like to do is insert an underscore at the front of the data
in each cell in a column - preferably in only those cells that have data. So
if the cell has ABCDEF in it it'd become _ABCDEF .

Any clues on how to accomplish this? Thanks.

That's helpful but I was hoping for something more automated. I should've
written that I have a lot of worksheets and a lot of columns to do this on.
This solution would still be fairly labor intensive and prone to errors or my
part.

Does anybody have a more automated method?

Cheers

"Gary''s Student" wrote:

Use a helper column:

Say your data is in column A. In B1 enter:

="_" & A1
and copy down. Then copy column B and paste/special/values back onto column
A.
--
Gary''s Student - gsnu200781


"Jon in Canby Or." wrote:

Sorry if this is really easy or not possible. I'm a dba with very little
Excel experience. I've been given an Excel spreadsheet to import into a
relational database there are some data consistency issues...

Anyway, what I'd like to do is insert an underscore at the front of the data
in each cell in a column - preferably in only those cells that have data. So
if the cell has ABCDEF in it it'd become _ABCDEF .

Any clues on how to accomplish this? Thanks.

Create a helper column to the right of your data column, and use the
following formula to add the underscore "_" as a prefix character (Modifying
the A1 cell address to match your worksheet layout):

=IF(ISBLANK(A1),"","_"&A1)

Copy down to the last data row then copy the entire formula column, click on
the first cell of the original data column (Cell A1 in the formula example),
then click EDIT in the menu, select PASTE SPECIAL and click the VALUES option
button. Click OK to complete the process and exit the dialog box. Delete
the helper column when done.
--
Kevin Backmann


"Jon in Canby Or." wrote:

Sorry if this is really easy or not possible. I'm a dba with very little
Excel experience. I've been given an Excel spreadsheet to import into a
relational database there are some data consistency issues...

Anyway, what I'd like to do is insert an underscore at the front of the data
in each cell in a column - preferably in only those cells that have data. So
if the cell has ABCDEF in it it'd become _ABCDEF .

Any clues on how to accomplish this? Thanks.

That's better for taking care of the blank columns but you must've missed my
reply to the last suggestion as they were both posted within minutes of
eachother.

"Kevin B" wrote:

Create a helper column to the right of your data column, and use the
following formula to add the underscore "_" as a prefix character (Modifying
the A1 cell address to match your worksheet layout):

=IF(ISBLANK(A1),"","_"&A1)

Copy down to the last data row then copy the entire formula column, click on
the first cell of the original data column (Cell A1 in the formula example),
then click EDIT in the menu, select PASTE SPECIAL and click the VALUES option
button. Click OK to complete the process and exit the dialog box. Delete
the helper column when done.
--
Kevin Backmann


"Jon in Canby Or." wrote:

Sorry if this is really easy or not possible. I'm a dba with very little
Excel experience. I've been given an Excel spreadsheet to import into a
relational database there are some data consistency issues...

Anyway, what I'd like to do is insert an underscore at the front of the data
in each cell in a column - preferably in only those cells that have data. So
if the cell has ABCDEF in it it'd become _ABCDEF .

Any clues on how to accomplish this? Thanks.

I would go with =IF(A1="","","_" & A1)

OP wanted to add the underscore just if A had a value.


Gord Dibben MS Excel MVP

On Tue, 29 Apr 2008 11:16:05 -0700, Gary''s Student
wrote:

Use a helper column:

Say your data is in column A. In B1 enter:

="_" & A1
and copy down. Then copy column B and paste/special/values back onto column
A.

Select a column and run this macro:

Sub JonInCanby()
For Each r In Selection
v = r.Value
If IsEmpty(v) Then
Else
r.Value = "_" & v
End If
Next
End Sub

It will apply the prefix in one swell foop!
--
Gary''s Student - gsnu200782


"Jon in Canby Or." wrote:

That's helpful but I was hoping for something more automated. I should've
written that I have a lot of worksheets and a lot of columns to do this on.
This solution would still be fairly labor intensive and prone to errors or my
part.

Does anybody have a more automated method?

Cheers

"Gary''s Student" wrote:

Use a helper column:

Say your data is in column A. In B1 enter:

="_" & A1
and copy down. Then copy column B and paste/special/values back onto column
A.
--
Gary''s Student - gsnu200781


"Jon in Canby Or." wrote:

Sorry if this is really easy or not possible. I'm a dba with very little
Excel experience. I've been given an Excel spreadsheet to import into a
relational database there are some data consistency issues...

Anyway, what I'd like to do is insert an underscore at the front of the data
in each cell in a column - preferably in only those cells that have data. So
if the cell has ABCDEF in it it'd become _ABCDEF .

Any clues on how to accomplish this? Thanks.

Perfect! That's exactly what I was looking for. Thanks for the help.

"Gary''s Student" wrote:

Select a column and run this macro:

Sub JonInCanby()
For Each r In Selection
v = r.Value
If IsEmpty(v) Then
Else
r.Value = "_" & v
End If
Next
End Sub

It will apply the prefix in one swell foop!
--
Gary''s Student - gsnu200782


"Jon in Canby Or." wrote:

That's helpful but I was hoping for something more automated. I should've
written that I have a lot of worksheets and a lot of columns to do this on.
This solution would still be fairly labor intensive and prone to errors or my
part.

Does anybody have a more automated method?

Cheers

"Gary''s Student" wrote:

Use a helper column:

Say your data is in column A. In B1 enter:

="_" & A1
and copy down. Then copy column B and paste/special/values back onto column
A.
--
Gary''s Student - gsnu200781


"Jon in Canby Or." wrote:

Sorry if this is really easy or not possible. I'm a dba with very little
Excel experience. I've been given an Excel spreadsheet to import into a
relational database there are some data consistency issues...

Anyway, what I'd like to do is insert an underscore at the front of the data
in each cell in a column - preferably in only those cells that have data. So
if the cell has ABCDEF in it it'd become _ABCDEF .

Any clues on how to accomplish this? Thanks.