excel query- enter number e.g 01458264000 in cell A & 03266.00 in cell B and is able to save as excel file?

Question

but when i copy this excel file text to notepad it shows as 01458264000 than space equal to 5 or 6 digits than 03266.00. can any one guide me what can i do so that these two figure are shown as one continue figure without having space between them in notepad file.

both the 1st & 2nd answer are good. 2nd solved my problem but in the first i couldn't understand which key to press after entering the formula. i tried the enter key but the result didn't come. hope someone will explain about 1st. please also guide whom to give full marks because the 1st is also right though i couldn't understand it fully because i am not an excel expert

i like to give full marks to radpoe even though he didn't explain that cell c should be format as general. qwertykph also deserve full marks as he taught me the easiest method. in the last as kc said he has the best answer, yes he explained beautifully even though in para 3 last line he said- enter but there enter is not working instead tab is working but i will give him full marks. i hope he will explain how to do it for several rows of diff numbers as i tried his method but in vain

i have changed my mind and choosing qwertykph answer the best as i am finding it easiest but kc and someone else can get best for my next question which will also start with 'excel query'. again thanks to all three.

Answer 1

Sam,

The answer from RadPoe is certainly accurate and would do the job. Yet I wondered what motivated you to ask such a question and it must be more than a few numbers to concatenate. I think what you truly need is full control of your text. You want your view in Excel to appear in notepad without losing characters (truncation) or extra spaces (tabs).

Here's how:

1) Select entire sheet.
2) Change font to Courier New, size 8pts.
3) Auto-size column widths. Click Format, Columns, AutoFit
4) Save file in .xls format
5) Save file in Formatted Text (Space Delimited) (*.prn)
6) Open the saved ".prn" file using notepad to view result
7) Repeat steps as necesary to fine tune results


You're probably wondering why Courier New 8pts. Simple reason, when using that font and size, the number of characters in a cell matches the cell width. So a 5-character string will fit in a cell with a width of 5. Its perfect for controlling the text layout when not in Excel format. It also means you can control the number of spaces between columns. For example, if you want EXACTLY 2 spaces between columns of data, simple insert a column, change width to 2 and then step 5 above.

So there you have it. Courier New 8pts. is truly the key to columnar text happiness. ;-)

Regards,
QwertyKPH @ Yahoo

Answer 2

If there is a genuine reason for not entering the numbers in one cell in the first place, then the easiest thing is to concatenate the two strings in another cell, using this formula: =A1&B1
This will result in one long string, and then just copy and paste it to Notepad, not the original two cells.

P.S.
The 5-6 digit space is actually just one character: a tab.

Answer 3

My advice as to the best answer is none of the above and none of the below. I am answering because I feel I have the best answer for you.

1. To enter numbers with leading zeros, you need to format the cell as text. Select the cell, do Format, Cell and choose Text, then OK. In your example, do that for Cell A (A1) and Cell B (B1).

2. To concatenate as the first answerer said, you need to use a third cell. I used C1 as this third cell for my test. This cell needs to be formatted General. Sometimes the formatting is carried over from the cells that are the source of the formula, so to insure the General format is set, select C1 and do Format, Cell and choose General, if it is not already.

3. Enter the formula into cell C1. You will be copying from C1 into Notepad, not from A1 and B1. Here's the formula: =A1&B1. Here is how to enter it: Select cell C1, Press = on the keyboard, Click cell A1 with your mouse, Press & on the keyboard (Shift 7), Click the cell B1 with your mouse, Press enter.

If you need this formula for several rows, copy it down and it will work in every row, just copy it in column C.

Now to get the 'one continuous figure with no space between them' into notepad, select the cell C1, Ctl - C to copy, Open Notepad and press Ctl - V to Paste and there you have it.

It is not necessary to put this interim cell directly next to the numbers you have entered in A1 and B1. If you don't want it printing on reports, just enter the formula in any column you want and then select your print area without including the interim cell. Just remember to copy from the interim cell, not the two separate cells when you copy and paste into Notepad.

There you are: the Best Answer to your question.