![]() Also, it will truncate any fractional numbers to only the integer portion. To perform a simple text to columns procedure you only need to use the. expression.TextToColumns(Destination, DataType,TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other,OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator,TrailingMinusNumbers) The code below relates to the data shown above. Note that it works on the selected cells - so you need to select the cells you want to convert before running it. Text to Columns Use the TextToColumns method of the Range object. If IsNumeric(vArray) Then vArray = Format(vArray, strPadding) With r.Resize(UBound(vArray, 1), UBound(vArray, 2)) ![]() If IsNumeric(vArray(i, j)) Then vArray(i, j) = Format(vArray(i, j), strPadding) LngPadLength = Application.InputBox("Enter how many zeros to pad numbers to", "Number Padding", Type:=1) 'limitation: if you process fractional numbers eg 1.5674 you will lose the fractional portion!!!ĭim vArray, i As Long, j As Long, r As Range, lngPadLength As Long, strPadding As String (it did not bump before - properly - so apologies if I have added it too many times! :() 'pad out' meaning changing say '7' to '07' (but still keeping text format)ĭugong* Auto Merged Post ** BUMP * True to have Excel consider consecutive delimiters as one delimiter. Specifies whether to use single, double, or no quotes as the text qualifier. The format of the text to be split into columns. If the range is larger than a single cell, the top left cell is used. could the macro be amended so that you can select an amount of columns, run the macro to pop up a box asking what you want it to 'pad out' to - for example, highlight say 5 cols, run macro & advise to 'pad out' to 2chars, THEN select another column, run macro and select 5 chars.? A Range object that specifies where Microsoft Excel will place the results. Maybe I am missing what you are saying.I can't use a custom number format on text can I.? - It doesn't appear to work once in text format!īasically (I don't have the knowledge to do this unfortunately ). IF the reason you are using Text is leading zeros, use a Custom Number format like: 00000 but how do you do it over a sheet that contains say 120 columns (i.e.: en masse).? ![]() Please help!* Auto Merged Post *I kept searching and have found a way to do it on 1 column. VBA Determine Start and End Column of a Merged Cell: VBA Excel’s Color Scheme: VBA Find the Maximum Value For Each Column in a Range: VBA Format Painter: VBA Get the Active Cell’s Column or Row: VBA Resize an Object to a Range Size: VBA Select All Cells: VBA Sum by Color: VBA Test if Selection is a Range. I know there is a really simple solution but I cannot for the life of me see it!!!!!! (I have attached a small example which shows the template, data to past and end result of what I require - the redtab sheets are attempts to paste! I have tried copy and paste special as values but to no avail. I have tried copy and pasting (the sheet is already formatted as text. We wish to copy information from other sheets/places and paste into the template sheet - but have it as text (with green triangle on it) We have a template sheet we use which has a sheet formatted as 'text' - it msu remain this way for another purpose. I know some things about excel but this simple thing is stumping us. Columns("B:B").I had a look for help but the help is all the opposite way - converting text to numbers. Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ Set wb = Workbooks.Open(VarPath & VarFileName) So I want to just format the entire column to be the same type in each workbook, and circumvent the issue. Excel at times insists on reformatting data that I enter into cells, e.g., converting '01234' to '1234', and this screws me up when I need to read the data back. Randall Arnold 'John Brock' wrote: I am creating an Excel workbook using VB.NET, and have run into a problem. In case you need to know what it is I am doing to help, I recieve workbooks from cliental that need to be imported into SQL, and if the data in a row is a different data type then I get some null values. Look up info on numberformat in Excel's VBA help for more detail. If you need more information please ask, as I will be happy to provide. Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _ :=Array(1, 2), TrailingMinusNumbers:=True Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ VarPath = Sheets("sheet1").Range("B2").Valueįor Each wsheet In ActiveWorkbook.Worksheets VarFileName = Sheets("sheet1").Range("A2").Value ![]() VarSavein = Sheets("sheet1").Range("C2").Value
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |