Converting Scientific format into text

[ankrups]

Hi,

I need to convert the format from whole columns from scientific format into text.

For example, I am receiving data from my different vendor where they provide all information in csv file. Upon receiving the file in CSV format, the barcode comes as per below formate.

SCREEN 1

I have around 8 different vendors where columns are in different places. What I need is make above format into text format as per below.
SCREEN 2

Currently I am using method as per below.
Select the whole column and then using Data>Text to columns > Selecting Delimited option and then clicking "Next" button three times. Then selecting "Text".

This process I need to do for every time and for each column which is very time consuming. I need solution that if I select any column in that tab and then run some kind of code or macro using keyboard shortcuts which can change the format to screen no2.

Thank you in advance.

 

[BSALV]

you'll need 8 macros, 1 for each vendor, and the lucky part, most of the job you can do yourself with the macro-recorder.
There is just the range to be adjusted and a little bit of "noise" to be deleted.

So can you use that macro-recorder, do the job once for a vendor and post the result (the recorded macro) here ?

 

[ankrups]

you'll need 8 macros, 1 for each vendor, and the lucky part, most of the job you can do yourself with the macro-recorder.
There is just the range to be adjusted and a little bit of "noise" to be deleted.

So can you use that macro-recorder, do the job once for a vendor and post the result (the recorded macro) here ?

Thanks for the quick reply. But I need universal approach, where one code fits for all.

Any other idea.

 

[BSALV]

this does 99% of the job.
You have to check what separator you use in the last line, adapt the source and destionation (otherwise it can be made with parameters)

Sub My_Text_to_Columns()

     'prepare array fieldinfo, ADAPT TO YOUR SITUATION*********************
     Dim myArray(1 To 100, 1 To 2)                              'preparing for 100 columns
     For i = 1 To UBound(myArray)                               'loop 100 times
          myArray(i, 1) = i                                     'column
          myArray(i, 2) = 2                                     'text=2
     Next
     'suppose you have exceptions !!!!!!
     myArray(5, 2) = 1                                          '5th column, general=1
     myArray(10, 2) = 4 '10th column is date
     myArray(15, 2) = 9 'skip 15th column
     '***********************************************************

     'text to columns, range A1:A1000 to B1, with ";" as separator for 100 times as text
     Range("A1:A1000").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo:=myArray, TrailingMinusNumbers:=True

End Sub

 

[Fluff]

How about

Sub ankrups()
   With Selection
      .TextToColumns .Rows(1), xlDelimited, xlDoubleQuote, False, False, False, False, False, False, , Array(1, 2)
   End With
End Sub

 

[Alex Blakenburg]

Another option might be to use @Domenic's code from the link below and open the CSV file using a macro and converting it to text on opening it:
Using a Loop (or similar) to assign FieldInfo when opening TXT in VBA?

 

[ankrups]

this does 99% of the job.
You have to check what separator you use in the last line, adapt the source and destionation (otherwise it can be made with parameters)

Sub My_Text_to_Columns()

     'prepare array fieldinfo, ADAPT TO YOUR SITUATION*********************
     Dim myArray(1 To 100, 1 To 2)                              'preparing for 100 columns
     For i = 1 To UBound(myArray)                               'loop 100 times
          myArray(i, 1) = i                                     'column
          myArray(i, 2) = 2                                     'text=2
     Next
     'suppose you have exceptions !!!!!!
     myArray(5, 2) = 1                                          '5th column, general=1
     myArray(10, 2) = 4 '10th column is date
     myArray(15, 2) = 9 'skip 15th column
     '***********************************************************

     'text to columns, range A1:A1000 to B1, with ";" as separator for 100 times as text
     Range("A1:A1000").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo:=myArray, TrailingMinusNumbers:=True

End Sub

excellent mate. This code worked very well and have used it for my one of the vendor. thank you very much.

 

[ankrups]

How about

Sub ankrups()
   With Selection
      .TextToColumns .Rows(1), xlDelimited, xlDoubleQuote, False, False, False, False, False, False, , Array(1, 2)
   End With
End Sub

thanks mate. in accordance with code provided from other member, this code also helped me a lot to use in my day to day work. thanks heaps.

 

[Fluff]

Glad we could help & thanks for the feedback.