Trim Problem

[3nduranc3]

Hey All!

I would have a minor question, I hope you know a solution. I have a colums, that the data for each cell is extracted from an SAP module. It is onlysimple text, but formats are a little strange. Those data cells which have longer text are separated with line breakw, and also there are a lot of spaces at the end and also line breaks. The column has a fix width, but when the whole table is formatted with my macro, there are unnecessary line breaks in each row, which take a lot of space. I was trying to remove these.

I tried to use TRIM function to do the work, but it seems the line breaks at the end still stays there. Also I would love if my macro could do the work, could you help me?

Many Thanks!

 

[sdsurzh]

Hi,

Try using clean formula.

=CLEAN(TRIM(A1))

Thanks,
SK

 

[Rick Rothstein]

Hey All!

I would have a minor question, I hope you know a solution. I have a colums, that the data for each cell is extracted from an SAP module. It is onlysimple text, but formats are a little strange. Those data cells which have longer text are separated with line breakw, and also there are a lot of spaces at the end and also line breaks. The column has a fix width, but when the whole table is formatted with my macro, there are unnecessary line breaks in each row, which take a lot of space. I was trying to remove these.

I tried to use TRIM function to do the work, but it seems the line breaks at the end still stays there. Also I would love if my macro could do the work, could you help me?

It is a little hard to make a suggestion without seeing the file, but let me try. Select the column with the data you described and then run this macro...

Sub FixSAPtext()
  Dim Addr As String
  Addr = Intersect(Selection, ActiveSheet.UsedRange).Address
  Range(Addr) = Evaluate("TRIM(SUBSTITUTE(SUBSTITUTE(" & Addr & ",CHAR(160),"" ""),CHAR(10),"" ""))")
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (FixSAPtext) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for.

 

[ramananhrm]

Pls try this function.

=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))

 

[3nduranc3]

Hi Rick,

Sorry not ti insert any files, but I'm not sure what I am allowed to exactly, and I don't want any trouble. Well, I used your vba code and it works fine in some cells, but in some it just ends up in #VALUE. I am not exactly sure why this is happening. Could it be that I import the files from an older version of Excel .xls (With value paste), and it formats are wrong?

I tried native characters what I use, and specials also like éáűúőó and !!++, but when I insert text in a new cell your vba code works fine.

Actually when I set a format simply to string, the cells extracted from SAP also make strange things. I have a lot of #-s. How could I make this work?

Thanks in advance!