Editing string based on length

[0nyx175]

Good morning, please see spreadsheet below. I get all my results from a CSV file. This isn’t a problem except for the first nine days of the month where excel cuts off the first 0 so 01012009 reads 1012009 and cannot be properly concatenated.

Date Name Item
1012009 Bob Keyboard
1012009 Sid Mouse
1012009 Dave Monitor
1012009 Ed Monitor
1012009 Andrew Keyboard
1012009 Dan Keyboard
1012009 Bruce Keyboard

What I would like to do is have VB read the contents of the date cell and if it is less than eight characters, edit the cell by adding a zero at the beginning of the string. This needs to be a one off process for all cells. Can anyone come up with code that might make this happen? Thanks, 0nyx

[sweep]

Hi,

highlight the cells you want to check and run this code

Please Login or Register  to view this content.

Which adds a 0 to the front of the data if it's 7 characters long. NB, the apostrophe is added to convert to text format to avoid truncating again.

[NBVC]

If you save the original file as a .txt file instead of .csv.. then when you open it in Excel.. a text wizard will help you import the file... Click Delimited in first window, Select Comma in second window, select the column from the Data Preview pane in the next window that corresponds to your part numbers...and then select Text from the Column Data Format area.... then click Finish...

Now you should have the data as you desired it.

[Richard Schollar]

Hi

Do you intend for the cell contents to be seen by Excel as a string rather than as a numeric value? If so, the following should work and will be reasonably quick:

Please Login or Register  to view this content.

Richard

[0nyx175]

Im having most luck with Sweep's code but I cant seem to build in a way to select all the cells, any ideas with that?

[0nyx175]

Ah nope I sorted it with

Sub a()
Range("E1:E1000").Select
Dim rCell As Range
For Each rCell In Selection
If Len(rCell.Value) = 7 Then rCell.Value = "'0" & rCell.Value
Next
End Sub

Thanks for everyone's help with this. I appreciate it.

0nyx

[0nyx175]

Ah no i didnt.. that brings up a runtime error six and highlights

If Len(rCell.Value) = 7 Then

[NBVC]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[0nyx175]

Apologies NBVC, i solved the problem so will go away now anyways.

Many thanks.

[NBVC]

Please remember to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save