How to remove empty space above the chart?

How to remove space between numbers

• I have thousand of entries on sheet like this 1978 3351 1234 5678 I want to remove space with help of command, he show me like this 1978335112345678 Best regard I have thousand of entries on sheet like this 1978 3351 1234 5678 I want to remove space with help of command, he show me like this 1978335112345678 Best regard I have thousand of entries on sheet like this 1978 3351 1234 5678 I want to remove space with help of command, he show me like this 1978335112345678 Best regard I have thousand of entries on sheet like this 1978 3351 1234 5678 I want to remove space with help of command, he show me like this 1978335112345678 Best regard

• Answer:

  khurram. so you have this value in a single cell 1978 3351 1234 5678 Normally, I would suggest selecting the cells and using the replace command to replace the spaces with nothing. The problem with this is then you would have a 16 digit credit card number - but since it is a number, it would be truncated bywhich only show 15 digits of precision for number. What you would need to do is put in a formula Assume the data is in column A. Insert a New column B if it is not blank. Select column B (whether you inserted a new one or not) and format it as TEXT Now Assume that the first number is in cell A1. In B1 put in =substitute(A1," ","") then drag fill this down the column (assume all these type numbers are in a single column. when done, select the column and do Edit=>copy then do Edit=>Paste special and select values this should put the 16 digit numbers in the cells as strings. this worked for me. use the substitute worksheet function =SUBSTITUTE(A4," ","") where A4 is your cell - do this in a helper column then copy and paste special values over the original data if required - though MUCH easier would be to select the column, use edit replace and replace all space characters by typing a single space in the what box with nothing ! Assume that you are having the data in Column-A and your data start from A1 cell like the below. A1 cell 1978 3351 1234 5678 Copy and paste the below formula in B1 cell. =SUBSTITUTE(A1," ","") Drag the B1 cell formula to the remaining cells of Column-B based on Column-A Data. Change the cell reference A1 in the above formula to your desired cell, if required. Hello Khurram, If you are sure that there are always FOUR numbers in each set. Also, if you are sure that there is only ONE space in between each set. Also, if there are no spaces at the very beginning of the whole string, and no space at the end, then this will work: Assume your data is in column A, starting with a1. enter this in b1 and drag down =LEFT(A1,4) & MID(A1,6,4) & MID(A1,11,4) & RIGHT(A1,4)