Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

=UPPER(TRIM(A1))
=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
add comment

3 Answers

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
add comment

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

=TRIM(SUBSTITUTE(A1, CHAR(160), " "))
share|improve this answer
add comment

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
1  
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.