Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • 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
gabrielPennyback
Asked:
gabrielPennyback
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
 
gabrielPennybackAuthor Commented:
Yes, please award the points to Patrick, his answer works perfectly.
Thanks,
John
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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