?
Solved

Need to remove extra white space from description field.

Posted on 2012-03-28
6
Medium Priority
?
336 Views
Last Modified: 2012-03-28
I have a description column which contains data like this :



"This unit uses the firemaster top, which prevents any accidental discharges.

The canister dimensions are 8 and seven-eight inches by 2 inches. An optional holster can be purchased.


 Utilizes the Firemaster Safety Top to prevent accidental discharge
 Contains 17% Oleoresin Capsicum which gives 2 million Scoville Heat Units
 Has a 3 year shelf life
 Can supply up to 150 one-second bursts
 Total range of stream is 25-30 feet
 Made in USA



Product : SW20"





The problem is I have to save this file as a "Tab Delimited" text file.

All the text in the description needs to be on "one line".

Is there anyway to remove all the extra white space in the description?  
thanks
0
Comment
Question by:MikeMCSD
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 8

Accepted Solution

by:
Leo Torres earned 1000 total points
ID: 37776948
try
something like

=trim(A1)

where A1 is the field you need trimmed
0
 
LVL 8

Expert Comment

by:Leo Torres
ID: 37776988
if you have carriage return on a line this should be help full as well

http://www.justanswer.com/computer-programming/27rt7-replace-carriage-return-excel-needs.html
0
 
LVL 12

Assisted Solution

by:kgerb
kgerb earned 1000 total points
ID: 37777098
1.  Select the cell(s) you wish to modify and press Ctrl+F (Find).
2.  In the Find what box enter Alt+0010.  This is the ASCII code for carriage return.  Nothing will visibly change but continue anyway.
3.  In the Replace with box enter two spaces, or a comma, or whatever you wish to replace the carriage returns.
4.  Click replace all and you should be finished!

Kyle

Correction:
Alt+0010 is Line Feed, Alt+0013 is Carriage Return.  You may need to run find and replace twice, one for each.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 16

Author Comment

by:MikeMCSD
ID: 37777317
thanks guys . . .

The Replace couldn't find those codes so I tried :

=SUBSTITUTE(C2, CHAR(13), " ") 'carriage returns

=SUBSTITUTE(C2, CHAR(10), " ")  'line feeds'

and they both worked the same.
Should I run both of them?
0
 
LVL 8

Expert Comment

by:Leo Torres
ID: 37777327
yes run then both
they are replacing different things.. and you want to get the shortest string possible correct..
0
 
LVL 16

Author Closing Comment

by:MikeMCSD
ID: 37777913
worked . . thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question