Formatting data in a column by adding prefix (1 Viewer)

JayR

Registered User.
Local time
Today, 05:21
Joined
Mar 21, 2007
Messages
19
Hello everyone. This should be an easy one for many of you, although I thought it would be easy for me as well.
I have a number of excel spread sheets whereby the data in one of the columns should be following a certain format, yet doesn't due to the way a third party entered the information. I need to make the data consistent before I can use the data for comparison against other spreadsheets and access tables.
What I encounter is data such as:

05-12345

When I require it to be:

GY-05-12345

In the same column, there is data with this format already, just not most of the data.

I tried selecting one cell as well as many cells to modify them to a custom format. I used 'help' and made a custom format of:

"GY-" 0

But this did nothing, although I could undo the changes that didn't appear to have happened.

I also tried:

"GY- " 0
"GY-"0
"GY- "0

Yet nothing happened.

What am I missing?

Thanks for any help you can spare me.

Jay
 

Brianwarnock

Retired
Local time
Today, 12:21
Joined
Jun 2, 2003
Messages
12,701
As some of the cells are correct then I don't see how a preformatted approach could help.
A simple manual approach would be to insert a column and then conditionally copy the cells before deleting the original.
eg assume data starts in A2 then insert a col to the right then in B2 enter the formula
=IF(LEFT(A2,2)="GY",A2,"GY-"&A2)
then fill down.
Delete Column A.


Of course this could be written as a macro and used in all sheets, probably the best long term solution, tho' if the col is the same size and place in each sheet then just do it as a group exercise on theworkbook.

Brian
 

JayR

Registered User.
Local time
Today, 05:21
Joined
Mar 21, 2007
Messages
19
Thank you for your quick response.

I have done as you outlined and it appears to work great except that when trying to compare the modified data, it is not a value but rather a formula. I wanted to cut/copy the modified data and paste it into another sheet for comparison.

Here is the actual issue at hand. We submit pathology cases to a central registry and have done so for years. It turned out that they were not accepting certain cases and didn't let us know. As a result it has fallen on my shoulders to reconcile the data and resubmit the cases which didn't get into the registry.....but first I have to determine how.

I can generate an access (or excel) list of cases that should have gone and was going to compare it against the worksheets/lists of cases the registry tells me that they have in their system. First problem that arose was that they did not follow a consistent format when entering the case numbers, which is why I needed a prefix on the cases as I cannot compare them otherwise. My next problem was the inability to move the actual values to another location.

So what I am trying to do is to take the information they provided me, modify the data to a more useable format, then move it into another worksheet in which I can combine other worksheets and subsequently convert to an access table for ultimately yield a list of cases that need to be sent to the registry.

I know a little long winded and maybe there is a better way, but this is what I am stuck with currently.

Thanks,
Jay
 

Brianwarnock

Retired
Local time
Today, 12:21
Joined
Jun 2, 2003
Messages
12,701
This little sub will do the editing for you. to make it flexible I did no hardcoge any range, just highlight all the cells then run the macro.
I coded it in thisworkbook so that it could run on all sheets.

Sub edit()
For Each c In ActiveWindow.RangeSelection
If Left(c, 2) <> "GY" Then
c.Value = "GY-" & c.Value
End If
Next c
End Sub

Brian
 

JayR

Registered User.
Local time
Today, 05:21
Joined
Mar 21, 2007
Messages
19
Sorry for the delayed response. I have not had time to work on this until now.
I have to admit I am lost at what you are suggesting that I do. As I only tinker with these applications as needed, my knowledge of them is about average.
What is a sub? How do I make that macro?
Sorry for the dumb questions.

Jay
 

JayR

Registered User.
Local time
Today, 05:21
Joined
Mar 21, 2007
Messages
19
Okay, now I feel like an idiot.

I again tried the original method you suggested and it should work as when I was copying the information, I realized I should have been using paste special to paste the information as data. Hence, I can add the prefix to the data needing it, then I can move it all into an access table and get this show on the road.

Thank you for all your patience and help.

Jay
 

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 07:21
Joined
Jul 5, 2007
Messages
586
Okay, now I feel like an idiot.

I again tried the original method you suggested and it should work as when I was copying the information, I realized I should have been using paste special to paste the information as data. Hence, I can add the prefix to the data needing it, then I can move it all into an access table and get this show on the road.

Thank you for all your patience and help.

Jay

If you're pasting from Excel into Access, you need not worry if the value is calculated in a formula or if it is an actual value.
when you paste into Access, only the value and number format (if any) is pasted into the table. If the number format (if any) conflicts with the data type in the Access table column, Access will give you an alert informing you.
However, for text fields, there is no problem pasting directly into Access without converting the formula to a value (assuming the length of the text field in Access is not smaller than the text being copied in Excel).
 

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 07:21
Joined
Jul 5, 2007
Messages
586
Hello everyone. This should be an easy one for many of you, although I thought it would be easy for me as well.
I have a number of excel spread sheets whereby the data in one of the columns should be following a certain format, yet doesn't due to the way a third party entered the information. I need to make the data consistent before I can use the data for comparison against other spreadsheets and access tables.
What I encounter is data such as:

05-12345

When I require it to be:

GY-05-12345

In the same column, there is data with this format already, just not most of the data.

I tried selecting one cell as well as many cells to modify them to a custom format. I used 'help' and made a custom format of:

"GY-" 0

But this did nothing, although I could undo the changes that didn't appear to have happened.

I also tried:

"GY- " 0
"GY-"0
"GY- "0

Yet nothing happened.

What am I missing?

Thanks for any help you can spare me.

Jay

I would consider speaking to the administrator/developer of the database into which these initial values are being input. Idealy, there should be no incorreect/incomplete values inserted. There should be a way to ensure all data entered by third parties conforms to the desired work product.
Although in this circumstance, correcting the values is relatively simple, it is time that could be saved if the developer clamps down on data entry.
 

Brianwarnock

Retired
Local time
Today, 12:21
Joined
Jun 2, 2003
Messages
12,701
Hi Jay
I forgot that you had to do a copy and paste special to complete the manual approach before deleting the original data. :eek:

The macro is the way to go.
Alt and F11 opens up the VB editor , select this workbook from the pane on the left and copy and paste the code into the pane on the right.

go back to the spreadsheet, select all of the rows you wish to change, select tools - macros , the macro and then run, it's easier than it sounds and as macros are useful, worth learning

Brian
 

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 07:21
Joined
Jul 5, 2007
Messages
586
Hi Jay
I forgot that you had to do a copy and paste special to complete the manual approach before deleting the original data. :eek:

The macro is the way to go.
Alt and F11 opens up the VB editor , select this workbook from the pane on the left and copy and paste the code into the pane on the right.

go back to the spreadsheet, select all of the rows you wish to change, select tools - macros , the macro and then run, it's easier than it sounds and as macros are useful, worth learning

Brian
I agree that VBA is the way to go, but since Jay has indicated the ultimate destination for the corrected data is in an Access database, wouldn't be better to use VBA from Access to automate the whole thing, including importing from the corrected Excel file(s)?

If a naming convention is used for the Excel files, or if they are always located in a particular folder, or even if the user needs to specify a path in a form, it should be relatively easy to open, edit, and import directly from Access. Also, once the edited data is imported, the Excel file can be closed without saving the changes, thusly protecting the original data values.

That code is located in the FAQ section and/or on Bob's pages.
 

Brianwarnock

Retired
Local time
Today, 12:21
Joined
Jun 2, 2003
Messages
12,701
In post 1 he said
I need to make the data consistent before I can use the data for comparison against other spreadsheets and access tables
So I assume that to correct the data in Excel is a prerequisite for everything that follows. If an audit trail is required then that has to be planned into the system, but I don't think that it is an issue for this.

Brian
 

Users who are viewing this thread

Top Bottom