Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a column which is very likely to have 0 or 1 space in the middle of it. I would like to remove it. A1 is the column while these forumulas are in A2, A3

=REPLACE(A2,FIND(" ",A2),1,"")

The problem is with find. I suspect it finds no space and errors. The below is my result. How do I delete all spaces in a row? or delete at least one space in the middle of the row?

123456  123456  #VALUE!
123 456 123 456 123456  
share|improve this question
up vote 3 down vote accepted

This will replace any spaces in A2, which I think is what you want (your question talks about both "cell" and "row"):

=SUBSTITUTE(A2," ","")
share|improve this answer

I removed the beginning "space" from an excel cell (after export data into an excel sheet) by the following:

share|improve this answer

a3 = right(a2,4) or however many number from the right to the gap

a4 = left(a2,2) or however many numbers from the left to the gap

a5 = a4&a3

Copy result and paste as value where required.

share|improve this answer
Far messier than the original solution, plus if the numbers to the right or left vary per row then this solution would be more time consuming to implement than manually removing the spaces – Joe Taylor Jan 24 '12 at 9:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .