• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Avoid droping the (') sign from CSV

Abhijeet R. Joshi

Abhijeet R. Joshi

Active Member
This is a problem that most of us must have faced...

So here is the problem, the CSV extract that I receive from Client has a ' sign thus not dropping the initial zeros from the values...however whenever I copy paste this in excel the initial zeros drop out and then my vlookup gets in a soup....

Hope we have an option to the below two:
1. Avoid dropping of zeros when copy pasting..
2. I have copied and pasted the values and lost the (') sign, now how do I get it back?
3. How to add (') without increasing the len of the values(eg. I had an earlier value with len of 5 now after I add the formula, I do not want the len to change <>5)...

Hope someone has an answer to this....:)
Thanks in advance..:):)
 
Thanks for the replies guys..

@bobhc: If I paste the values I lose the intial zeros whereas if I paste it all, I can have this ....but my work asks me to paste the values thus dropping the zeros..

@Atul: I cannot share an file, since this is client sensitive data....Sorry for that...:)
 
Abhijeet R. Joshi said:
Thanks for the replies guys..

@bobhc: If I paste the values I lose the intial zeros whereas if I paste it all, I can have this ....but my work asks me to paste the values thus dropping the zeros..

@Atul: I cannot share an file, since this is client sensitive data....Sorry for that...:)
Click to expand...

Can't you supply us with a sample? We don't actually need the sensitive data ;)
 
Sorry Guys, I had this encountered this recently...So I am not actually able to find a sample of this..:(
Just was came across a similar post somewhere on internet so thought of having this resolved..
I am trying to get a sample data, once I have one will post the same here..
Apologies on the inconvenience caused...:)
 
Have you really checked point 3?

In a cell if I have following content:
1000
it gives me length 4 if I check with LEN formula.

Now if I edit this cell and make the input as
'1000
Then cell gets formatted as 'TEXT' and LEN formula still gives 4.

Supposing that you want values padded up by zeroes and formatted as text, try to code like below:
Code: 
Public Sub PadWithZeroes()
Dim r As Range
For Each r In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If Len(r.Value) <> 0 And Len(r.Value) <= 5 Then
        r.Value = "'" & Format(r.Value, "00000")
    End If
Next r
End Sub
 
Shri, I recently discovered that your code works only for 4 digits numericals...
Can you please help me with a code that will work irrespective to the digits in the numericals?
Original After Code
2502 02502
367 00367
125468 125468
Click to expand...
 
The code posted above won't change anything for cells with more than 5 characters. What do you want to do with those cells?
 
Apologies on the late response...:)
The code is actually adding some zeros prior to the '3' character cells.

For instance: if my cell have 367 this will give me an output as '00367, thus not providing the expected results('367)
 
Hi, Abhijeet R. Joshi!

Give a look at the uploaded file. Can you copy and rename the copy as .csv? These forums don't allow their upload.

Its content is:
Number;text
0;'00000
1;'0001
2;'000000002
3;'000000000003
4;'0000004
5;'5

If you open the .csv from Excel you'd get displayed the same.

Now if you open the .txt you'd be prompted with the text import wizard. click Next, check Semicolon as separator, select Apostrophe as Text Qualifier, click Next.
a) If you click on End you'd get displayed this, all numeric:

Number text
0 0
1 1
2 2
3 3
4 4
5 5
b) if you select the 2nd column at below preview pane, you select Text option and you click on End you'd get displayed this, numeric and text respectively:

Number text
0 00000
1 0001
2 000000002
3 000000000003
4 0000004
5 5

Does it help? Otherwise consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!
 

Attachments

Abhijeet R. Joshi said:
Apologies on the late response...:)
The code is actually adding some zeros prior to the '3' character cells.

For instance: if my cell have 367 this will give me an output as '00367, thus not providing the expected results('367)
Click to expand...
Are you sure this means the same as your post #1 for which the code was posted?
 
Abhijeet,
Your posts #1, #9 do not match with what you require right now [post #11] which is confusing me.

In post #8 in before after sample 367 gets padded up by 2 zeroes and which isn't what you want per post #11. If the end goal is just to convert number containing cells to ones containing single quote then following should work:

Code: 
Public Sub PadWithZeroes2()
Dim r As Range
'Will change formatting of cells containing numbers
For Each r In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If IsNumeric(r.Value) And Len(r.Value) > 1 Then
        r.Value = "'" & r.Value
    End If
Next r
End Sub

If this isn't meeting your requirements then please post a manually built before / after sample [your office data is not something we really want to see :)] and ideology behind the changes.
 
Hi Guys,
Sorry for the delayed response, was on a trip this weekend...:p
I know I am asking a lot from you guys, but I sincerely appreciate your help on this...

@SirJB7,
I agree with your use of text delimiter, but since my data has got various columns that needs to be converted to text this will be a lengthy and time consuming on my part..

@ Shri:
Sorry for confusing you, and for not posting correctly (earlier there were only 5 characters that was expected but recently there is a variety that I can see ranging from 3- 15 characters).
It's not only padding the values with a (') but to also regain the lost zeros, is there a way that excel can store how many zeros were that for a value while opening and then just regain it once the macro is triggered??

Thanks a ton guys....:)
 
I'd suggest using SirJB7's method or

1. Open CSV in notepad or you can use a text editor of your choice.
2. Format the Excel column as Text
3. Copy and Paste the data from Notepad and paste it in Excel.

It will still retain its zeroes.

You can use VBA as well to handle this file data import.
 
This is not a CSV file on my system, this is an extract that is run from an application and directly opens...
I am trying to connect to the developers to have an option to save this in xls format or save on my system as CSV.

You can use VBA as well to handle this file data import.
Click to expand...
Shri, can you help me with any VBA code that might help on this?

Or is there a way to text all the columns in the text to columns delimiter, via VBA or some function?
Manually selecting all the columns and then shifting them to text will be too tedious...:(
 
Hi,

you can use the Macro Recorder during opening the file
and well respond to questions from the Import Assistant …

Other way - for a VBA developer - is to read the file in memory via an array variable
with needed columns in text in the worksheet …
​
 
Abhijeet R. Joshi said:
And sorry guys I cannot upload a file atleast as of now, since I am in my office..
Will try to load a dummy file once I reach home...:)
Click to expand...
Yesterday at 7:06 AM GMT-3)
Abhijeet R. Joshi said:
This is not a CSV file on my system, this is an extract that is run from an application and directly opens...
I am trying to connect to the developers to have an option to save this in xls format or save on my system as CSV.


Shri, can you help me with any VBA code that might help on this?

Or is there a way to text all the columns in the text to columns delimiter, via VBA or some function?
Manually selecting all the columns and then shifting them to text will be too tedious...:(
Click to expand...
Today at 7:59 AM

Hi, Abhijeet R. Joshi!
Now 12:55 PM... Still at office?
Regards!
 
Abhijeet R. Joshi said:
@SirJB7,
I agree with your use of text delimiter, but since my data has got various columns that needs to be converted to text this will be a lengthy and time consuming on my part..
Click to expand...
Hi, Abhijeet R. Joshi!

You always have the option of copy & paste... :p... or read & type... :D... or wake up from the bed, be a little less lazy, turn on your computer, open Excel, start the macro recorder (yes, it comes built-in with Excel), do it -the lengthy and time consuming thing- manually (once), stop the macro recorder (yes, it comes without a magician, it's scheduled for version 3030, if not hurried you might wait), and give a look to what it magically (well, no magician but a list a couple of rabbits) brings up: :mad:
Code: 
    Workbooks.OpenText Filename:= _
        "Avoid droping the (') sign from CSV (for Abhijeet R. Joshi at chandoo.org).txt" _
        , Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 2)), TrailingMinusNumbers:=True

Regards!

PS: And no, I'm not gonna tell you how to manage to handle the update for more than one column as B in the sample file. ;)
 
Thanks SirJB7, for the sarcastic explanation...:mad::D

Sorry forgot to upload the file today as well....dont worry I am not getting old just doing lots of FORCROs...:p....
 
Hi, Abhijeet R. Joshi!
Sarcastic explanation? I don't know what you're talking about... :rolleyes: I just read kind, patient and humble suggestions :)
I'd still stick to NFS :cool:
Regards!
 
You must log in or register to reply here.
Back
Top