Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell data
Hi to all.
I need some help with exporting excel data to text file, I have data in cells, I have a code for only returning max of 12 characters, this is fine and working, but the cells that have below 12 characters I need these cells to padded out with spaces so that when I export to a text file it looks like columns in the text file. Can anyone help me with this Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell data
myString = Left(myString & Space(12), 12)
"santaviga" skrev i meddelelsen ... Hi to all. I need some help with exporting excel data to text file, I have data in cells, I have a code for only returning max of 12 characters, this is fine and working, but the cells that have below 12 characters I need these cells to padded out with spaces so that when I export to a text file it looks like columns in the text file. Can anyone help me with this Regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell data
How do I input this into a cell to operate???
Thanks "Lazzzx" wrote: myString = Left(myString & Space(12), 12) "santaviga" skrev i meddelelsen ... Hi to all. I need some help with exporting excel data to text file, I have data in cells, I have a code for only returning max of 12 characters, this is fine and working, but the cells that have below 12 characters I need these cells to padded out with spaces so that when I export to a text file it looks like columns in the text file. Can anyone help me with this Regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell data
In the macro code (you are using a macro, right?) that is now returning your
max of 12 characters, replace whatever is doing the truncating (it should be using the Left function) with the code line Lazzzx gave you (substituting the variable name or cell value reference used in your current formula for the myString example variable Lazzzx gave you; assigning it to whatever variable you are currently assigning it to). Usually if you are going to refer to code (or a formula on the spreadsheet side of things), it is a good idea to post that code (or formula) rather than make an off-hand reference to it; that way, we can post the modification to it instead of giving a general example for you to try and implement. Rick "santaviga" wrote in message ... How do I input this into a cell to operate??? Thanks "Lazzzx" wrote: myString = Left(myString & Space(12), 12) "santaviga" skrev i meddelelsen ... Hi to all. I need some help with exporting excel data to text file, I have data in cells, I have a code for only returning max of 12 characters, this is fine and working, but the cells that have below 12 characters I need these cells to padded out with spaces so that when I export to a text file it looks like columns in the text file. Can anyone help me with this Regards |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell data
I am not using a macro, my company doesn't allow them, is there any other way
to do this?? "Rick Rothstein (MVP - VB)" wrote: In the macro code (you are using a macro, right?) that is now returning your max of 12 characters, replace whatever is doing the truncating (it should be using the Left function) with the code line Lazzzx gave you (substituting the variable name or cell value reference used in your current formula for the myString example variable Lazzzx gave you; assigning it to whatever variable you are currently assigning it to). Usually if you are going to refer to code (or a formula on the spreadsheet side of things), it is a good idea to post that code (or formula) rather than make an off-hand reference to it; that way, we can post the modification to it instead of giving a general example for you to try and implement. Rick "santaviga" wrote in message ... How do I input this into a cell to operate??? Thanks "Lazzzx" wrote: myString = Left(myString & Space(12), 12) "santaviga" skrev i meddelelsen ... Hi to all. I need some help with exporting excel data to text file, I have data in cells, I have a code for only returning max of 12 characters, this is fine and working, but the cells that have below 12 characters I need these cells to padded out with spaces so that when I export to a text file it looks like columns in the text file. Can anyone help me with this Regards |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell data
I figured you were using a macro because you said you have "code" (a
formula, assuming that is what you meant, is not code) and you posted your question in a programming newsgroup. Anyway, referencing my last paragraph for emphasis... how are you now returning your maximum of 12 characters (that is, what is the "code" or formula being used)? Rick "santaviga" wrote in message ... I am not using a macro, my company doesn't allow them, is there any other way to do this?? "Rick Rothstein (MVP - VB)" wrote: In the macro code (you are using a macro, right?) that is now returning your max of 12 characters, replace whatever is doing the truncating (it should be using the Left function) with the code line Lazzzx gave you (substituting the variable name or cell value reference used in your current formula for the myString example variable Lazzzx gave you; assigning it to whatever variable you are currently assigning it to). Usually if you are going to refer to code (or a formula on the spreadsheet side of things), it is a good idea to post that code (or formula) rather than make an off-hand reference to it; that way, we can post the modification to it instead of giving a general example for you to try and implement. Rick "santaviga" wrote in message ... How do I input this into a cell to operate??? Thanks "Lazzzx" wrote: myString = Left(myString & Space(12), 12) "santaviga" skrev i meddelelsen ... Hi to all. I need some help with exporting excel data to text file, I have data in cells, I have a code for only returning max of 12 characters, this is fine and working, but the cells that have below 12 characters I need these cells to padded out with spaces so that when I export to a text file it looks like columns in the text file. Can anyone help me with this Regards |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell data
Hi Rick,
Sorry for the misunderstanding. The formula i'm using is the following: =LEFT(Sheet1!A1,12) This returns the text in that cell to a max of 12, but I need this to pan out to 12 characters if this cell only contains 5 characters so it will be placed in a text file as if in colums. Regards "Rick Rothstein (MVP - VB)" wrote: I figured you were using a macro because you said you have "code" (a formula, assuming that is what you meant, is not code) and you posted your question in a programming newsgroup. Anyway, referencing my last paragraph for emphasis... how are you now returning your maximum of 12 characters (that is, what is the "code" or formula being used)? Rick "santaviga" wrote in message ... I am not using a macro, my company doesn't allow them, is there any other way to do this?? "Rick Rothstein (MVP - VB)" wrote: In the macro code (you are using a macro, right?) that is now returning your max of 12 characters, replace whatever is doing the truncating (it should be using the Left function) with the code line Lazzzx gave you (substituting the variable name or cell value reference used in your current formula for the myString example variable Lazzzx gave you; assigning it to whatever variable you are currently assigning it to). Usually if you are going to refer to code (or a formula on the spreadsheet side of things), it is a good idea to post that code (or formula) rather than make an off-hand reference to it; that way, we can post the modification to it instead of giving a general example for you to try and implement. Rick "santaviga" wrote in message ... How do I input this into a cell to operate??? Thanks "Lazzzx" wrote: myString = Left(myString & Space(12), 12) "santaviga" skrev i meddelelsen ... Hi to all. I need some help with exporting excel data to text file, I have data in cells, I have a code for only returning max of 12 characters, this is fine and working, but the cells that have below 12 characters I need these cells to padded out with spaces so that when I export to a text file it looks like columns in the text file. Can anyone help me with this Regards |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell data
Assuming you want the padding on the right, try using this instead...
=LEFT(A1&REPT(" ",12),12) If you want the padding on the left (as maybe for numbers), then you would use this formula... =RIGHT(REPT(" ",12)&A1,12) Rick "santaviga" wrote in message ... Hi Rick, Sorry for the misunderstanding. The formula i'm using is the following: =LEFT(Sheet1!A1,12) This returns the text in that cell to a max of 12, but I need this to pan out to 12 characters if this cell only contains 5 characters so it will be placed in a text file as if in colums. Regards "Rick Rothstein (MVP - VB)" wrote: I figured you were using a macro because you said you have "code" (a formula, assuming that is what you meant, is not code) and you posted your question in a programming newsgroup. Anyway, referencing my last paragraph for emphasis... how are you now returning your maximum of 12 characters (that is, what is the "code" or formula being used)? Rick "santaviga" wrote in message ... I am not using a macro, my company doesn't allow them, is there any other way to do this?? "Rick Rothstein (MVP - VB)" wrote: In the macro code (you are using a macro, right?) that is now returning your max of 12 characters, replace whatever is doing the truncating (it should be using the Left function) with the code line Lazzzx gave you (substituting the variable name or cell value reference used in your current formula for the myString example variable Lazzzx gave you; assigning it to whatever variable you are currently assigning it to). Usually if you are going to refer to code (or a formula on the spreadsheet side of things), it is a good idea to post that code (or formula) rather than make an off-hand reference to it; that way, we can post the modification to it instead of giving a general example for you to try and implement. Rick "santaviga" wrote in message ... How do I input this into a cell to operate??? Thanks "Lazzzx" wrote: myString = Left(myString & Space(12), 12) "santaviga" skrev i meddelelsen ... Hi to all. I need some help with exporting excel data to text file, I have data in cells, I have a code for only returning max of 12 characters, this is fine and working, but the cells that have below 12 characters I need these cells to padded out with spaces so that when I export to a text file it looks like columns in the text file. Can anyone help me with this Regards |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell data
thanks a lot Rick, works a treat.
Regards "Rick Rothstein (MVP - VB)" wrote: Assuming you want the padding on the right, try using this instead... =LEFT(A1&REPT(" ",12),12) If you want the padding on the left (as maybe for numbers), then you would use this formula... =RIGHT(REPT(" ",12)&A1,12) Rick "santaviga" wrote in message ... Hi Rick, Sorry for the misunderstanding. The formula i'm using is the following: =LEFT(Sheet1!A1,12) This returns the text in that cell to a max of 12, but I need this to pan out to 12 characters if this cell only contains 5 characters so it will be placed in a text file as if in colums. Regards "Rick Rothstein (MVP - VB)" wrote: I figured you were using a macro because you said you have "code" (a formula, assuming that is what you meant, is not code) and you posted your question in a programming newsgroup. Anyway, referencing my last paragraph for emphasis... how are you now returning your maximum of 12 characters (that is, what is the "code" or formula being used)? Rick "santaviga" wrote in message ... I am not using a macro, my company doesn't allow them, is there any other way to do this?? "Rick Rothstein (MVP - VB)" wrote: In the macro code (you are using a macro, right?) that is now returning your max of 12 characters, replace whatever is doing the truncating (it should be using the Left function) with the code line Lazzzx gave you (substituting the variable name or cell value reference used in your current formula for the myString example variable Lazzzx gave you; assigning it to whatever variable you are currently assigning it to). Usually if you are going to refer to code (or a formula on the spreadsheet side of things), it is a good idea to post that code (or formula) rather than make an off-hand reference to it; that way, we can post the modification to it instead of giving a general example for you to try and implement. Rick "santaviga" wrote in message ... How do I input this into a cell to operate??? Thanks "Lazzzx" wrote: myString = Left(myString & Space(12), 12) "santaviga" skrev i meddelelsen ... Hi to all. I need some help with exporting excel data to text file, I have data in cells, I have a code for only returning max of 12 characters, this is fine and working, but the cells that have below 12 characters I need these cells to padded out with spaces so that when I export to a text file it looks like columns in the text file. Can anyone help me with this Regards |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell data
Hi Rick,
If I were to put this into a macro so it will do what i require with that, how would the code be written. Any ideas. Regards "Rick Rothstein (MVP - VB)" wrote: Assuming you want the padding on the right, try using this instead... =LEFT(A1&REPT(" ",12),12) If you want the padding on the left (as maybe for numbers), then you would use this formula... =RIGHT(REPT(" ",12)&A1,12) Rick "santaviga" wrote in message ... Hi Rick, Sorry for the misunderstanding. The formula i'm using is the following: =LEFT(Sheet1!A1,12) This returns the text in that cell to a max of 12, but I need this to pan out to 12 characters if this cell only contains 5 characters so it will be placed in a text file as if in colums. Regards "Rick Rothstein (MVP - VB)" wrote: I figured you were using a macro because you said you have "code" (a formula, assuming that is what you meant, is not code) and you posted your question in a programming newsgroup. Anyway, referencing my last paragraph for emphasis... how are you now returning your maximum of 12 characters (that is, what is the "code" or formula being used)? Rick "santaviga" wrote in message ... I am not using a macro, my company doesn't allow them, is there any other way to do this?? "Rick Rothstein (MVP - VB)" wrote: In the macro code (you are using a macro, right?) that is now returning your max of 12 characters, replace whatever is doing the truncating (it should be using the Left function) with the code line Lazzzx gave you (substituting the variable name or cell value reference used in your current formula for the myString example variable Lazzzx gave you; assigning it to whatever variable you are currently assigning it to). Usually if you are going to refer to code (or a formula on the spreadsheet side of things), it is a good idea to post that code (or formula) rather than make an off-hand reference to it; that way, we can post the modification to it instead of giving a general example for you to try and implement. Rick "santaviga" wrote in message ... How do I input this into a cell to operate??? Thanks "Lazzzx" wrote: myString = Left(myString & Space(12), 12) "santaviga" skrev i meddelelsen ... Hi to all. I need some help with exporting excel data to text file, I have data in cells, I have a code for only returning max of 12 characters, this is fine and working, but the cells that have below 12 characters I need these cells to padded out with spaces so that when I export to a text file it looks like columns in the text file. Can anyone help me with this Regards |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell data
Well, it depends on what exactly you want macro to do. Do you want to put
the entries from one column into another the way your formula does now? Do you physically modify the entry right there within the cell the entry is placed in? If the latter, do want this to happen at the time the entry is typed in? Is the entry even being typed in or is it the result of a formula? Something else maybe? Rick "santaviga" wrote in message ... Hi Rick, If I were to put this into a macro so it will do what i require with that, how would the code be written. Any ideas. Regards "Rick Rothstein (MVP - VB)" wrote: Assuming you want the padding on the right, try using this instead... =LEFT(A1&REPT(" ",12),12) If you want the padding on the left (as maybe for numbers), then you would use this formula... =RIGHT(REPT(" ",12)&A1,12) Rick "santaviga" wrote in message ... Hi Rick, Sorry for the misunderstanding. The formula i'm using is the following: =LEFT(Sheet1!A1,12) This returns the text in that cell to a max of 12, but I need this to pan out to 12 characters if this cell only contains 5 characters so it will be placed in a text file as if in colums. Regards "Rick Rothstein (MVP - VB)" wrote: I figured you were using a macro because you said you have "code" (a formula, assuming that is what you meant, is not code) and you posted your question in a programming newsgroup. Anyway, referencing my last paragraph for emphasis... how are you now returning your maximum of 12 characters (that is, what is the "code" or formula being used)? Rick "santaviga" wrote in message ... I am not using a macro, my company doesn't allow them, is there any other way to do this?? "Rick Rothstein (MVP - VB)" wrote: In the macro code (you are using a macro, right?) that is now returning your max of 12 characters, replace whatever is doing the truncating (it should be using the Left function) with the code line Lazzzx gave you (substituting the variable name or cell value reference used in your current formula for the myString example variable Lazzzx gave you; assigning it to whatever variable you are currently assigning it to). Usually if you are going to refer to code (or a formula on the spreadsheet side of things), it is a good idea to post that code (or formula) rather than make an off-hand reference to it; that way, we can post the modification to it instead of giving a general example for you to try and implement. Rick "santaviga" wrote in message ... How do I input this into a cell to operate??? Thanks "Lazzzx" wrote: myString = Left(myString & Space(12), 12) "santaviga" skrev i meddelelsen ... Hi to all. I need some help with exporting excel data to text file, I have data in cells, I have a code for only returning max of 12 characters, this is fine and working, but the cells that have below 12 characters I need these cells to padded out with spaces so that when I export to a text file it looks like columns in the text file. Can anyone help me with this Regards |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell data
Hi,
Well what I require is that I have data in a number of cells in a workbook, and what I need it for that data to be exported when I want to a text file for report issues. The data has to be exported in its columns to a max of 12 characters but if there is less than 12 charcters in a cell this needs to be padded out so that it look s like its in columns and systematic to read in a text file. Looks like below. data data data data data data data data it must be displayed like this in the text file and so on it works with the code you gave me last night as a formula but I would like to try as a macro. Regards "Rick Rothstein (MVP - VB)" wrote: Well, it depends on what exactly you want macro to do. Do you want to put the entries from one column into another the way your formula does now? Do you physically modify the entry right there within the cell the entry is placed in? If the latter, do want this to happen at the time the entry is typed in? Is the entry even being typed in or is it the result of a formula? Something else maybe? Rick "santaviga" wrote in message ... Hi Rick, If I were to put this into a macro so it will do what i require with that, how would the code be written. Any ideas. Regards "Rick Rothstein (MVP - VB)" wrote: Assuming you want the padding on the right, try using this instead... =LEFT(A1&REPT(" ",12),12) If you want the padding on the left (as maybe for numbers), then you would use this formula... =RIGHT(REPT(" ",12)&A1,12) Rick "santaviga" wrote in message ... Hi Rick, Sorry for the misunderstanding. The formula i'm using is the following: =LEFT(Sheet1!A1,12) This returns the text in that cell to a max of 12, but I need this to pan out to 12 characters if this cell only contains 5 characters so it will be placed in a text file as if in colums. Regards "Rick Rothstein (MVP - VB)" wrote: I figured you were using a macro because you said you have "code" (a formula, assuming that is what you meant, is not code) and you posted your question in a programming newsgroup. Anyway, referencing my last paragraph for emphasis... how are you now returning your maximum of 12 characters (that is, what is the "code" or formula being used)? Rick "santaviga" wrote in message ... I am not using a macro, my company doesn't allow them, is there any other way to do this?? "Rick Rothstein (MVP - VB)" wrote: In the macro code (you are using a macro, right?) that is now returning your max of 12 characters, replace whatever is doing the truncating (it should be using the Left function) with the code line Lazzzx gave you (substituting the variable name or cell value reference used in your current formula for the myString example variable Lazzzx gave you; assigning it to whatever variable you are currently assigning it to). Usually if you are going to refer to code (or a formula on the spreadsheet side of things), it is a good idea to post that code (or formula) rather than make an off-hand reference to it; that way, we can post the modification to it instead of giving a general example for you to try and implement. Rick "santaviga" wrote in message ... How do I input this into a cell to operate??? Thanks "Lazzzx" wrote: myString = Left(myString & Space(12), 12) "santaviga" skrev i meddelelsen ... Hi to all. I need some help with exporting excel data to text file, I have data in cells, I have a code for only returning max of 12 characters, this is fine and working, but the cells that have below 12 characters I need these cells to padded out with spaces so that when I export to a text file it looks like columns in the text file. Can anyone help me with this Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i find specific data in a cell and its colocated cell data | Excel Worksheet Functions | |||
converting numerical data in one cell to word data in another cell | Excel Worksheet Functions | |||
how to find number in a cell , a cell contains character data ornumeric data | Excel Worksheet Functions | |||
Find MAX data in sheet (Cell) - Any cell that exceeds X amount of data | Excel Discussion (Misc queries) | |||
data validation to restrict input in cell based on value of cell above that cell | Excel Programming |