Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make field Fixed lenght
Hi there,
I have following formula in sheet2 A1 ="H"&Sheet1!B3&Sheet1!D3 Which I get HABC TEST20060817, but is it possible to always make the customer name field lenght 30? HABC TEST 20060817 HXYZRAY TEST 20060817 The actual customer name might be 7 digits, 10 digits or 15. So I'm trying to make it fixed lenght of 30 digits. B3 is the customer name field. Would appreciate any info anyone might have. Not sure if this can be done. Thank you, J |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make field Fixed lenght
="H" & Sheet1!B3 & LEFT(" ",30 - LEN("H" &
Sheet1!B3) - LEN(Sheet1!D3)) & Sheet1!D3 HTH Charles Juan wrote: Hi there, I have following formula in sheet2 A1 ="H"&Sheet1!B3&Sheet1!D3 Which I get HABC TEST20060817, but is it possible to always make the customer name field lenght 30? HABC TEST 20060817 HXYZRAY TEST 20060817 The actual customer name might be 7 digits, 10 digits or 15. So I'm trying to make it fixed lenght of 30 digits. B3 is the customer name field. Would appreciate any info anyone might have. Not sure if this can be done. Thank you, J |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make field Fixed lenght
You can use the LEFT string function directly in the cell.
i.e =LEFT("H"&Sheet1!B3&Sheet1!D3,30) -- Les Torchia-Wells "Juan" wrote: Hi there, I have following formula in sheet2 A1 ="H"&Sheet1!B3&Sheet1!D3 Which I get HABC TEST20060817, but is it possible to always make the customer name field lenght 30? HABC TEST 20060817 HXYZRAY TEST 20060817 The actual customer name might be 7 digits, 10 digits or 15. So I'm trying to make it fixed lenght of 30 digits. B3 is the customer name field. Would appreciate any info anyone might have. Not sure if this can be done. Thank you, J |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make field Fixed lenght
Hello Charles/Les,
thanks for the quick reply. Charles, I changed yours to ="H" & Sheet1!B3 & LEFT(" ",30 - LEN(Sheet1!B3)) & Sheet1!D3 Which gives me exactly what I want. Your code was giving me 21, I think becuase You were including the H and Date. But was able to figure it out. So thank you both of you for the help. Have a good weekend. J "Die_Another_Day" wrote: ="H" & Sheet1!B3 & LEFT(" ",30 - LEN("H" & Sheet1!B3) - LEN(Sheet1!D3)) & Sheet1!D3 HTH Charles Juan wrote: Hi there, I have following formula in sheet2 A1 ="H"&Sheet1!B3&Sheet1!D3 Which I get HABC TEST20060817, but is it possible to always make the customer name field lenght 30? HABC TEST 20060817 HXYZRAY TEST 20060817 The actual customer name might be 7 digits, 10 digits or 15. So I'm trying to make it fixed lenght of 30 digits. B3 is the customer name field. Would appreciate any info anyone might have. Not sure if this can be done. Thank you, J |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I sum fields down through a fixed field value. | Excel Discussion (Misc queries) | |||
fixed cell lenght | Excel Discussion (Misc queries) | |||
A fixed field format needs fronting zeroes for each cells value. | Excel Worksheet Functions | |||
How to make a field created a part of the Pivot Table Field List? | Excel Discussion (Misc queries) | |||
Export file to CSV delimited with fixed field length | Excel Discussion (Misc queries) |