Text To Columns Macro (fixed Width) With Predefined Format On Result
May 18, 2009
I need to fixed width-text to column macro and found a reply in the forum.
However, when I apply the macro, the result of zeros in front of figures disappear since the format of value in splitted column doesn't predefined as text
e.g. sample text to split to column:
000122042009ABCDEFG00567
Required result:
0001|22042009|ABCDEFG|00567
when running below macro; result shows:
1|22042009|ABCDEFG|567
(Beginning zeros figures of the first and last column disappear)
Applied Macro:
View 3 Replies
ADVERTISEMENT
Dec 21, 2007
I need the different categories to be separated into separate columns, but they do not appear to be separated by anything useful or a fixed width. Not only that, but the last few columns were moved onto a second line when I copied the data into Excel for some reason. The data originally came over in an Adobe Acrobat file. This is what the final product should look like: ....
View 9 Replies
View Related
Apr 28, 2008
I am having a problem importing fixed width text files with VBA. I created a user form for a dept to import a number of reports we get as text files into Excel. I imported each line into 1 cell (Column A) in Excel and then used mid functions to split out the columns. Everything works fine on my PC. However, when I use other PCs to import the same files (either off the same shared drive or the PC hard drive) the columns are not splitting in the correct places. Does anybody know what could cause this. Since the columns are splitting based off a fixed number of characters, I'm assuming Excel is somehow distorting a character count while opening the text file. I did notice that if I try to import text files saved on one of our shared drives, they do not import correctly on my PC either. However, if I copy and save the same text file to any of the other shared drives or my PC, it imports with no problems.
View 2 Replies
View Related
Aug 21, 2014
How to insert the column dividers manually--for importing a fixed-width text file into excel? The files I'm having to import consist of dozens and dozens of columns.
View 3 Replies
View Related
Jul 22, 2009
I'm trying to create a vb to get all columns on my sheet to have a fixed width.
If I run my sub all columns go back to the set width. But I want the width to reset when the width is changed.
View 2 Replies
View Related
Nov 19, 2008
Excel 2007
I want to CONCATENATE 3 columns
column 1 will have names
Columns 2 and 3 will have numbers
All will be of different lengths
Is there any way to set a fixed width when I use the CONCATENATE funstion so everything looks more uniform (left justified)
View 7 Replies
View Related
Jan 23, 2009
I have a worksheet that I receive as an Excel File in this format:
Account # (10 digits), Blank, Amount (Various), Date, Number(6), Location(blank), Tran Code (2) and Description (5)
View 10 Replies
View Related
Apr 4, 2012
I have this data below in an excel worksheet. I'm using the ws.Cells(x,x).Value command to read values from the cells an print them out to a .prn fixed width file. However when it writes it to the .prn file my numeric fields don't have are not 0.00 but 0. And also the numeric fields are left justified I would like them right justified in the .prn file.
Excel Spreadsheet values
1.135.102/29/120.000.001.135.202/29/120.000.00
My output to .prn file looks like below:
1.135.1 02/29/12 0 0
1.135.1 02/29/12 0 0
I would like my output to .prn file to look like the following:
1.135.1 02/29/12 0.00 0.00
1.135.1 02/29/12 0.00 0.00
View 7 Replies
View Related
Jan 24, 2014
I'm trying to make this macro work to resize images for our fashion Look Book. In theory, it should work. But of course it does not Here is the code we are using:
Code:
Sub BIGcrop()
Selection.Height = 507.6
x = Selection.ShapeRange.Width
y = x / 2
Z = y - 144
[Code]....
I want all my pictures to have a height of 7.05 inches and a width of 4. The height comes out different everytime depending on the scaling of the template I'm using. I need it to adjust accordingly. I'm open to having the image move into merged cells, as long as it will first adjust the height and then crop the sides (so the orientation stays the same).
View 4 Replies
View Related
Apr 22, 2014
I had posted earlier to see if there was a way to protect certain cells from being updated when using a linked excel spreadsheet to access. Since there have been no replies I'm assuming not.
Different approach, can I create a Macro that would run after the update that would insert my required text into each cell from A2 through to R2.
The text would always be the same and always in exactly the same cells. This would need to be an insert into and not a replacement of the data already in those cells.
A1 B1 C1 D1
State
BUName
InsuranceCompanyName
Claim
ROnumber
Renter Last Name
[Code] .......
A2 B2 C2 D2
CategoryFilter CategoryFilter CategoryFilter NoFilter
The text under each of the A2, B2, C2 etc is what I would want the macro to do for me, essentially putting back what the update from the linked source removed.
View 3 Replies
View Related
Jul 20, 2012
I have 5 columns with data in each
I want to create a 6th column that looks to the columns on the left with data in ti and concatenates all data in the 5 columns and puts it into one cell in the 6th column however put a space between each break of data so that it can be distinguished which bit of data was in what column previously.
The challenge is the new 6th column can only contain 30 characters - When it exceeds 30 characters then create a 7th column and put the rest of data in the 7th column, again the 7th column can only have 30 characters so if exceeds this then put the remaining characters in a 8th column
There will never be more than a total of 90 characters in the original 5 columns so there will only need to be scope for a maximum of 3 additional columns
So for example
Column A had two words in it that totaled 20 characters (the space between the two words is also counted as a character)
Column B had two words in it that totaled 20 characters (the space between the two words is also counted as a character)
Column C had a word that contained 10 characters
Column D had a word that contained 5 characters
Column E had a word that contained 10 characters
Then the result would be
Column F would only have the data originally held in Column A (because it can't include Column B's data as this would exceed the 30 characters)
Column G would have data that was originally held in column B and column C - with a space between B and C data
Column H would have data that was originally held C, D and E - with a space between C, D and E data
Another point to consider is if in one of the orginal 5 columns had say 3 words in it and lets say the 3rd word is the word that exceeds the 30 character limit, then the whole of the third word is to be carried oved to the next new column, I can't have words cut in hlaf with one half in Column 'F' and the other half in Column 'H' for example.
View 1 Replies
View Related
Oct 26, 2012
I have a long (190,000) list of customer data, all in Column A (unfortunately with blank rows among it, but working now to fix that).
Down the column, individual customers are bookended by a "adf" and a "/adf". (these have open and close brackets like HTML code, but I cannot reproduce them in this forum).
For each customer, I need to find the rows that begin with (brackets spelled out since I do not know how to show them):
1. [open bracket] vehicle status
2. name part=3D"first"[close bracket]
3. [open bracket]name part=3D"last"[close bracket]
4. [open bracket]email[close bracket]
5.[open bracket]phone time=3D
6.[open bracket] name part=3D
And transpose only those rows it into columns.There is a dynamic number of rows for each customer, so there's no way to simply count and transpose, as the columns would all be mis-entered.Somehow it needs to recognize those 6 row items, and transpose those values only, with the and the only telltale of a start and finish of a specific customer.
EDIT: How about a macro to delete all rows except those that contain those partial values above?
View 3 Replies
View Related
Oct 22, 2008
I require a bit of code that locates where the excel file is stored - it then searches that directory folder for all file names. Any file names with an extension *.hm for example are listed in column A, while their relevant 'Dates of last Modification' are listed in Column B.
Every time the spreadsheet opens it should ask the user if he/she requires a re-search of this directory and update of any file names accordingly. NB: if a file name is deleted from the directory, the file name should remain in the spreadsheet.
View 3 Replies
View Related
May 21, 2007
way to get a formula result to freeze or remain unchanged AFTER a certain time?
I tried using a macro which runs when the file is opened and does a crude 'copy/paste values' operation and replaces the Formula results with their values, but its is not very 'nice'!
I need a table to change a pattern of values but not in the past, but still to remember what that value was....!
View 9 Replies
View Related
Apr 10, 2013
I'm trying to get something like - If a cell contains certain text AND certain text then display set text OR If a cell contain certain text AND certain text then display set text.
For example:
Vauxhall Corsa 1.5Litre Petrol = vauxhall corsa petrol
Vauxhall Corsa 1.5Litre Deisel = vauxhall corsa Deisel
Vauxhall Nova 1 litre Petrol = vauxhall nova petrol
So it the formula is effectively searching for the car type and the fuel type and then returning the relevant set text. So far I'ver only been able to find IF functions criteria to identify one feature of the text.
I'm thinking it's got to be variation on =IF(ISNUMBER(FIND("Vauxhall Corsa",A1)),"Vauxhall Corsa Petrol") but there should be something else in there like =IF(ISNUMBER(FIND("Vauxhall Corsa"&"Petrol",A1)),"Vauxhall Corsa Petrol")
I know this is wrong but just trying illustrate my thinking. I need it to pick up vauxhall corsa AND petrol. I understand that I would have to stick a lot of IF functions together in a string, but that is fine as long as I can ask it to pick certain text AND certain text within a cell.
View 6 Replies
View Related
Jun 23, 2009
I want B1 to copy from A1, but if A1 contains a certain text string, then remove that text string and keep everything else. For example, I want B1 to remove "ru" "la" "fm" and "mu" so if A1 is 5464ru-xl then B1 will return 5464-xl or if A1 is 36944la-s then B1 returns 36994-s
View 14 Replies
View Related
Jan 5, 2010
I have open a workbook and copy the data from that workbook to another workbook.
Here the problem is workbook name is not fixed. How to oepn that workbook.
I used the belwo statement but giving error "no file name"
View 4 Replies
View Related
Aug 30, 2013
Can I use a macro to format a column that contains the words 'Grand Total'?
When I paste over the pivot it has reduced the column width and since it is all being produced by a macro I dont want to be lazy and do manual formatting.
View 9 Replies
View Related
Jan 11, 2010
I need to know how to format columns in a spreadsheet. What I want to do is autofit the columns, all except for Column A; I want that want at a fixed width of 9. The code below is my attempt to do this, and it's not resulting as I wish. Stepping through the code, it performs the first part beautifully and autofits all of the columns. Where I have the line
View 2 Replies
View Related
Jun 25, 2014
I've seen a lot of traffic around running macro's on a clock at regular intervals. But what if I want to run a Macro (Macro1) at 8:55, 9:55, 11:55, 12:55, 13:55, 16:55 and another Macro (Macro2) at the top of each of those hours? The string of times is not consecutive, so I have built a table to define the release times. At each of these times I need to run a macros, and I'm not sure where to begin.
View 3 Replies
View Related
Oct 28, 2012
I was wondering if i could arrange some sets of data (on one sheet) in fixed format and create a new sheet for each set of data using VBA
I have some sets of data in sheet (similar to the sheet "Raw data" of the attached file)
first four rows contain costumer-1 details,
next few rows contain bill details of costumer-1,
again,
next four rows contain costumer-2 details,
next few rows contain bill details of costumer-2,
and
table goes on for around 10-20 costumers
Now, I want to arrange the given set of data of each costumer into a fixed format (as sheet "reminder pad" of the attached file) and create a new sheet for each costumer
Also I want to add reference no to each sheet (with automatic increment for each sheet)
View 4 Replies
View Related
Jun 23, 2014
I need to extract all instances of words that have format xnnnnnn, where x is an alpha character (letter of alphabet, to be precise) and nnnnnn are numbers. The words could something like u435586. The problem is I do not know how many instances of these words are in the string. The entire string is contained in a cell. A sample string could be something like:
SMNTv922970;#1283;#SMNT 433925;#1284;#SQRS 003417;#1285;#SQRSp047683;#1286;#SMNT 6132451;#1287;#SQRSw3145627;#1288
and the end result should be
v922970 t433925 t003417 p047683 t6132451 w3145627
The words are preceded by the character "" which might facilitate the search.
View 2 Replies
View Related
May 22, 2007
I have a workbook which has a variety of command buttons with assigned macros, one of which is a product detail button which redirects to a new worksheet.
Is it possible to be able to click on the button, display a new window (the new worksheet), but only display a section of it?
For example, I have 5 products. Each of the five products are detailed on the same worksheet and I have a button for each. I would like Button 1 to open up the details for product 1 only and button 2 to open up product 2 details etc.
I could go to individual sheets, but if I had 2000 products, the book would be a little large.
View 3 Replies
View Related
Sep 4, 2012
How can i make blank columns be certain width using VBA... I need to do this because the data that is populated in a sheet will be different every time i need the blank columns to be certain width.
View 2 Replies
View Related
Nov 17, 2012
1. Is there a faster way to adjust the size of the columns? I had to use my mouse to stretch the column out. This can be quiet time consuming if there are 8 columns needing to adjust to the same size. ie Col A, B,I, J needs to be the same size.
2. Can Excel determine the best size for the column? Say I need a column with data saying "yes" and "no" and a column for the letter "X" only or do I have to stretch the width of the column myself?
View 1 Replies
View Related
Oct 30, 2012
I have a spreadsheet that allows users to paste set data from a PDF Image (using OCR) straight into Excel and then use the MID function to split the data accordingly.
Unfortunately, the OCR isn’t too intuitive and gets it wrong sometimes.
So to counter this, in another sheet (in the same workbook) I have a manual input section, and a simple macro button that pastes this data into the same fields where the OCR text would be, so that the main sheet works exactly the same way as before.
The problem is, and most likely due to the simplicity of the sheet, if a combination of OCR pasting and manual inputting is used, when I hit the paste button, it over rides the OCR data with blank cells
In the link below I have shown what is currently happening (1, 2, 3), and an example of what I would actually like it to do (4, 5, 6).
Example - Online Spreadsheets - EditGrid
So, in the 2nd scenario, I would like “5” to recognise that the respective cells in “4” already contain data and fill them ‘Grey’. This I have already achieved with basic conditional formatting.
However, I need to take it 1 step further and say that if the parent sheets cell (Auto OCR) contains data, as well as filling cells (in sheet Manual) lock these cells off and prevent the end user from adding data and/ or being copied over to the parent sheet.
Is this possible?
The result then being the parent sheet with both OCR text and copied text from the manual input sheet.
Both sheets are protected anyway and only allow for user input in certain areas, so is it even possible to apply further protection once the sheet is locked already?
View 1 Replies
View Related
Dec 6, 2013
Im new to excel and problem regarding the sumproduct function.
I have three columns in my sheet1, column A and column B contain 1000 numbers.
Hence, column A goes from A1 to A1000 and column B from B1 to B1000.
In the first row of column C, i want to use sumproduct on A1:A5 with B1:B5.
My problem arises when I want to drag the formula down, in the second row of column C I want
to use sumproduct on A6:A10 with B6:B10 but the formula uses A2:A6 with B2:B6.
Is there a way to fix the ranges when I am using sumprodct so it takes "fixed" ranges like every fifth,
A1:A5 with B1:B5 , A6:A10 with B6:B10, A11:A15 with B11:B15 and so on.
View 2 Replies
View Related
Jan 13, 2010
I'm looking for a macro or function/formula to list & total a range of column widths. Having lots of columns with varying widths t'would rather not have to do it manually from each column's Properties.
View 9 Replies
View Related
Jun 24, 2009
i have a unique problem. I have a spreadsheet with 2 worksheets.
Worksheet A
Dlr# Dlr Name Zip Code
X123 Dane's 50266
X123 Dane's 50266
X123 Dane's 53135
X983 Andy's 50254
X983 Andy's 50254
X348 Ryan's 45678
So, this spreadsheet has some duplicate rows and some unique ones. All are needed as duplicate zips mean that the dealer is sending out multiple mailings to the same zip code.
My task is to transfer these into Worksheet B so that duplicate zips go in different columns but unique zips don't. So this is what Worksheet B will look like:
Worksheet B
Dlr# Dlr Name Drop 1 Drop 2 Drop 3
X123 Dane's 50266 50266
X123 Dane's 53135
X983 Andy's 50254 50254
X348 Ryan's 45678
My spreadsheet has over 500 dealers with over 1500 rows of data in it, so if there is an automated way to create spreadsheet B.
View 9 Replies
View Related
Oct 6, 2013
I have just been sent a strange file, which I have attached, on its only sheet columns "A" and "B" have data entered in them, but they are not visible. You can access them if you select column "C" and use the left arrow key, but all attempts at unhiding and changing the column width are fuitless.
I have tried selecting the ENTIRE sheet, then Home>Format>Unhide Columns but it does nothing I try selecting the entire sheet, then Home>Format>Column Width and setting it to something, like "20", but again it is fruitless.
I even tried using a macro in the "Immediate Window" to ask Excel whether the columns are hidden (by using "?Columns(1).Hidden" and it returned "False") then I used a similar macro ("Cells.EntireColumn.ColumnWidth = 12.75") and again - nothing! :O
View 3 Replies
View Related
