• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 437
  • Last Modified:

VBA that will replace line skips with commas

I have a bunch of cells with line skips in them, as shown below only with the square symbol displayed after each number. I need a simple code that will convert all of them to commas followed by a space, as in: 17A, 17K, 18A ... etc.

Thanks,
John




... NO POWER 15H
17A
17K
18A
18C
18G

Open in new window

0
John Carney
Asked:
John Carney
1 Solution
 
JPIT DirectorCommented:
I believe you can go to edit and replace and copy the symbol you want to remove and paste it into the find what and replace it with the comma.
0
 
Patrick MatthewsCommented:
John,A simple formula will do it, assuming you mean ANSI 10 for the "line skip" (that's what Excel uses for an in-cell line break).=SUBSTITUTE(A2,CHAR(10),", ")In code you could use the Replace method, as below.Patrick
ActiveSheet.UsedRange.Replace Chr(10), ", "
 
' note how the Excel function is CHAR while the VBA function is Chr  :)

Open in new window

0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Yes, please award the points to Patrick, his answer works perfectly.
Thanks,
John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now