I just want to remove a breaking space I have after all the names in one cell.
So for example, a cell has
Mary Jane
Ashley Kate
Joe Bob
in one cell and there's a breaking space as shown, how do I remove that breaking space only? I only want to remove that breaking space after all the names in each cell for an entire column (not any of the spaces or breaking space between each name).
Last edited by shockwavemasta; 06-11-2014 at 09:03 AM.
whats a breaking space - cant see anything in the example
if you mean spaces before or after the text - use =TRIM()
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
A breaking space is basically a new line space (alt+enter). Trim does not work because it trims all the spaces between each breaking space of the names.
so my cell currently looks like this:
Mary Jane (breaking space)
Bob Joe (breaking space)
Evans Ron (breaking space)
(breaking space here)
cellexample.png
Last edited by shockwavemasta; 06-11-2014 at 09:50 AM.
Instead of TRIM use this:
=SUBSTITUTE(A1,CHAR(160),"")
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Still does not fix the problem because it deletes the breaking spaces in between the names.
Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Hi,
=SUBSTITUTE(A1,CHAR(10),"",LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")))
Regards
Attached workbook:
test.xlsx
Try this
=LEFT(A1,LEN(A1)-1)
It's the same output as before.
Sorry - can you clarify to whom you were replying?Originally Posted by shockwavemasta
Regards
No, it is not. See it on "After" sheet.
=IF(ISNUMBER(SEARCH(CHAR(10),A1)),SUBSTITUTE(A1,CHAR(10),"",LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))),A1)
if a single name has always a char 10 at the end then you can leave of the if part
just
=SUBSTITUTE(A1,CHAR(10),"",LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")))
mind you i cant see why
=LEFT(A1,LEN(A1)-1) as suggested above by Alkey wouldn't work
unless in your original there are some spaces after the last char 10
=LEFT(TRIM(A1),LEN(TRIM(A1))-1)
Last edited by martindwilson; 06-11-2014 at 11:04 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
To everybody:
I'm sorry it still doesn't work. For (martin and alkey), when I paste in that formula it leaves me with a long chain of words and not what you have shown. I copied the same formula into your sheet and it came out with the names connected in one line. Same for X0R LX (on it not working).
cellexample.png
Last edited by shockwavemasta; 06-11-2014 at 11:11 AM.
After you entered formula click on Wrap Text on the ribbon.
You need to wrap text in the cell with the formula ...
Entia non sunt multiplicanda sine necessitate
Works, thanks!
You're welcome and thank you for the feedback!
Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
Last edited by AlKey; 06-11-2014 at 11:47 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks