Top Contributors in Excel:

Andreas Killer - Ashish Mathur - Jeovany CV - HansV MVP - Bob Jones AKA: CyberTaz

Replacing Carriage Return

I am trying to export address information from our Accounting Software so that I can import it into our Shipping Software.

In our Accounting Software, the address information is contained in one box (no line 1, line 2, etc...) and you hit control-return to add a new line. When I export an address is more than one line into Excel, it looks like this:

I have tried doing a find and replace function with both Control-010 and Alt-010, but this is what I get:

Any ideas on why it replaces one of the characters (box with ? in it), but not the other one?

This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread.

Replies (11)

HansV MVP

Select the range you want to convert.

Press Alt+F11 then Ctrl+G to activate the Immediate window in the Visual Basic Editor.

Type or copy / paste the following line:

Selection.Replace vbCr, ""

With the insertion point anywhere in that line, press Enter.

Switch back to Excel.

---
Kind regards, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Ashish Mathur

Article Author

Hi,

Try this

1. Copy the data to Notepad

2. Copy the special character in Notepad and press Ctrl+H

3. Press Ctrl+V in the Find what box

4. Click on Replace All

Hope this helps.

Regards,

Ashish Mathur
www.ashishmathur.com
http://twitter.com/excelashish

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

jaihawk8

Unfortunately, neither of these worked for me. This is what I get when I try to do the visual basic idea:

A1 is the original

A3 is the original copies and then the visual basic applied to it:

It looks like that creates a multi line (within the same cell) entry, and there is still one of those special characters.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

HansV MVP

Try the following (using the example from your screenshot):

In B1, enter the formula =CODE(MID(A1,17,1))

In C1, enter the formula =CODE(MID(A1,18,1))

What is the result of these formulas?

---
Kind regards, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

jaihawk8

Try the following (using the example from your screenshot):

In B1, enter the formula =CODE(MID(A1,17,1))

In C1, enter the formula =CODE(MID(A1,18,1))

What is the result of these formulas?

B1 = 13

C1 = -10

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

HansV MVP

Are you sure about the - in -10?

---
Kind regards, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

jaihawk8

Are you sure about the - in -10?

Yes

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

HansV MVP

OK, try this in the Immediate window in the Visual Basic Editor (after selecting the appropriate range in Excel):

Selection.Replace ChrW(-10), ""

---
Kind regards, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

jaihawk8

OK, try this in the Immediate window in the Visual Basic Editor (after selecting the appropriate range in Excel):

Selection.Replace ChrW(-10), ""

Hans,

I tried that and this time nothing happened with the cell itself (it remained unchanged). Would it be helpful for me to attach a file with one of the records in it for you to look at? It looks like I can't do that here, but perhaps I could share one with you on Google Drive or Dropbox?

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

HansV MVP

Yes, that would be helpful.

---
Kind regards, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Question Info

Last updated October 5, 2021 Views 288 Applies to:

Replacing Carriage Return

Replies (11) 

Question Info

Replies (11)