Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
FRS
 
Posts: n/a
Default NEED HELP-----Removing a space at the end of a string of character

I have spreadsheets with tens of thousands of fields containing data. All of
the data consists of a string of characters followed by a space. In the end
I need to save the spreadsheet in a .csv format, WITHOUT the space in each
field.

Can anyone suggest a quick way to do this? Your help is appreciated.
  #2   Report Post  
Posted to microsoft.public.excel.misc
Jim May
 
Posts: n/a
Default NEED HELP-----Removing a space at the end of a string of character

In Excel there is a function TRIM()
example =trim(a1)
would eliminate any spaces before or after the text in a1.
HTH


"FRS" wrote:

I have spreadsheets with tens of thousands of fields containing data. All of
the data consists of a string of characters followed by a space. In the end
I need to save the spreadsheet in a .csv format, WITHOUT the space in each
field.

Can anyone suggest a quick way to do this? Your help is appreciated.

  #3   Report Post  
Posted to microsoft.public.excel.misc
Kevin B
 
Posts: n/a
Default NEED HELP-----Removing a space at the end of a string of character

The following suggestion assumes that the data is in Sheet 1, and starts at A1.

In Sheet 2, cell A1 insert the following formula:

=TRIM(Sheet1!A1)

Copy the formula down column A, as far down as the last row of data in Sheet
1. Then copy all of column A in Sheet 2 across as many columns as needed to
reference the entire data set in Sheet 1.

While still in sheet 2, press Ctrl + Shift + * to select the entire regions.
Copy the selected cells and then paste special as Values. You can then use
sheet 2 for your csv file source.
--
Kevin Backmann


"FRS" wrote:

I have spreadsheets with tens of thousands of fields containing data. All of
the data consists of a string of characters followed by a space. In the end
I need to save the spreadsheet in a .csv format, WITHOUT the space in each
field.

Can anyone suggest a quick way to do this? Your help is appreciated.

  #4   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default NEED HELP-----Removing a space at the end of a string of character

Put this macro in your personal.xls and then select the data and run the
macro

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


if indeed the only spaces are at the end and nowhere else where they might
be needed you could also do an editreplace and replace space with nothing
(don't put anything in the replace box)

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"FRS" wrote in message
...
I have spreadsheets with tens of thousands of fields containing data. All
of
the data consists of a string of characters followed by a space. In the
end
I need to save the spreadsheet in a .csv format, WITHOUT the space in each
field.

Can anyone suggest a quick way to do this? Your help is appreciated.



  #5   Report Post  
Posted to microsoft.public.excel.misc
FRS
 
Posts: n/a
Default NEED HELP-----Removing a space at the end of a string of chara

Appreciate all the help.

I tried the editreplace as it seemed to be the easiest solution and put a
space in the Find What field and nothing in the Replace field. That did not
work. It only worked for the few fields in which there was no character
string and just a space.

Will proceed to try the other suggestions.

================================================== ========

"Peo Sjoblom" wrote:

Put this macro in your personal.xls and then select the data and run the
macro

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


if indeed the only spaces are at the end and nowhere else where they might
be needed you could also do an editreplace and replace space with nothing
(don't put anything in the replace box)

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"FRS" wrote in message
...
I have spreadsheets with tens of thousands of fields containing data. All
of
the data consists of a string of characters followed by a space. In the
end
I need to save the spreadsheet in a .csv format, WITHOUT the space in each
field.

Can anyone suggest a quick way to do this? Your help is appreciated.






  #6   Report Post  
Posted to microsoft.public.excel.misc
FRS
 
Posts: n/a
Default NEED HELP-----Removing a space at the end of a string of chara

Am I doing something wrong with the editreplace option?
=============================================

"FRS" wrote:

Appreciate all the help.

I tried the editreplace as it seemed to be the easiest solution and put a
space in the Find What field and nothing in the Replace field. That did not
work. It only worked for the few fields in which there was no character
string and just a space.

Will proceed to try the other suggestions.

================================================== ========

"Peo Sjoblom" wrote:

Put this macro in your personal.xls and then select the data and run the
macro

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


if indeed the only spaces are at the end and nowhere else where they might
be needed you could also do an editreplace and replace space with nothing
(don't put anything in the replace box)

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"FRS" wrote in message
...
I have spreadsheets with tens of thousands of fields containing data. All
of
the data consists of a string of characters followed by a space. In the
end
I need to save the spreadsheet in a .csv format, WITHOUT the space in each
field.

Can anyone suggest a quick way to do this? Your help is appreciated.




  #7   Report Post  
Posted to microsoft.public.excel.misc
FRS
 
Posts: n/a
Default NEED HELP-----Removing a space at the end of a string of chara

I tried this solution and ran the TRIM function from Sheet1 on to Sheet 2.
Did the copy and paste special as values. The sheet2 fields still show the
space at the end of the character strings.
================================================== ==

"Kevin B" wrote:

The following suggestion assumes that the data is in Sheet 1, and starts at A1.

In Sheet 2, cell A1 insert the following formula:

=TRIM(Sheet1!A1)

Copy the formula down column A, as far down as the last row of data in Sheet
1. Then copy all of column A in Sheet 2 across as many columns as needed to
reference the entire data set in Sheet 1.

While still in sheet 2, press Ctrl + Shift + * to select the entire regions.
Copy the selected cells and then paste special as Values. You can then use
sheet 2 for your csv file source.
--
Kevin Backmann


"FRS" wrote:

I have spreadsheets with tens of thousands of fields containing data. All of
the data consists of a string of characters followed by a space. In the end
I need to save the spreadsheet in a .csv format, WITHOUT the space in each
field.

Can anyone suggest a quick way to do this? Your help is appreciated.

  #8   Report Post  
Posted to microsoft.public.excel.misc
Mike Middleton
 
Posts: n/a
Default NEED HELP-----Removing a space at the end of a string of chara

FRS -

Am I doing something wrong with the editreplace option? <


Perhaps the "spaces" are really non-breaking spaces.

If so, in the "Find and Replace" dialog box, in the "Find what:" edit box,
hold down the Alt key and on the numeric keypad type 0160, and leave the
"Replace with:" edit box blank.

- Mike
www.mikemiddleton.com


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
removing a space from starting of the text shikha Excel Worksheet Functions 3 September 24th 05 11:16 PM
removing a space between words in a cell JenBasch Excel Worksheet Functions 3 September 20th 05 12:39 AM
Setting character attributes in a text string Greg Neill Excel Worksheet Functions 1 June 15th 05 08:47 PM
Removing ' character from cells Don Excel Discussion (Misc queries) 5 December 21st 04 06:41 PM
Removing a space within a cell JERRY8 Excel Worksheet Functions 3 December 2nd 04 04:22 AM


All times are GMT +1. The time now is 11:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"