•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Can I split columns by number of characters/ digits?
Our take
If you're dealing with a list of zip codes that have lost their hyphen, there are effective methods in Excel to restore or manipulate the data without tedious manual edits. You can use functions like `LEFT`, `RIGHT`, and `MID` to split the zip codes into separate columns or reformat them to include the hyphen after the fifth digit. Alternatively, if the last four digits are not essential, simply extracting the first five digits can streamline your mail merge process.
I have a list of addresses for a mailing I need to complete. The zip codes column is supposed to read #####-####, but for whatever reason, the hyphen was lost and I'm left with all 9 digits straight. Is there a function I can use to either separate the columns, add the hyphen after the 5th digit, or even just get rid of the last 4 digits altogether?
I just need something I can work with for a mail merge, without manually having to change the couple hundred entries. The last 4 digits are nice, but not necessary. I tried using text to columns but since there is no delimitator, I didn't get far.
Any advice is appreciated! TIA
- Excel Version Office 365 version 2603; Build 19822.20114
- Excel Environment: desktop, Windows
- Excel Language English
- Your Knowledge Level: Formerly intermediate/advanced, but its been almost a decade since it was a daily work task, I'm extremely rusty
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to create a macro that removes unnecessary digits in zip code?Hello all! I format lots of files per day and would like to create a macro that automates most of it. I'm aware that using "Text to Columns" with a specific fixed width and then not importing the second half works to remove the extra four digits, but can't figure out how I would go about coding that. The recording process does not work, unfortunately. For context, I've included an example of what the zips might look like- all zips should end up in a 5 digit format, with leading zeroes if the zip is only 4 digits. The zip codes will always be in the N column, and the left and right columns also have data in them. As you can see, nine digit zip codes may be in 00000-0000 or 000000000 or 00000 0000. I've tried reformatting all zips to be 00000-0000 with the zeroes trailing for 5 digit zips, but I cant seem to use the delimited option in text to columns to get rid of the extra ones either. Any advice appreciated! https://preview.redd.it/jyphzamf4lig1.png?width=1144&format=png&auto=webp&s=b3cf7ad30618cc2b788d2c9c459e8e56ee6110f4 submitted by /u/ImportantDress7777 [link] [comments]
- How do I produce a TEXTSPLIT such as in the bottom case in the picture?https://preview.redd.it/8d6jfp5jebhg1.png?width=1080&format=png&auto=webp&s=653aa82a5dd82cc08a02cc72d37a0d56b976bed0 Hello, I have only cursory knowledge on Excel/Calc logic. I have a variable cell containing a string containing elements separated by hyphens, which I intend to spill across columns, but it stops working properly (Case 3) when there is only one element since there are no hyphens. I tried going around it by adding double hyphens to trigger the TEXTSPLIT, but an empty cell is created. How can I make so that the result returns #N/A errors on unused "slots?" Thank you! submitted by /u/Luxenroar [link] [comments]
Tagged with
#Excel alternatives for data analysis#natural language processing for spreadsheets#Excel compatibility#Excel alternatives#generative AI for data analysis#financial modeling with spreadsheets#rows.com#enterprise-level spreadsheet solutions#natural language processing#zip codes#Excel#mail merge#columns#hyphen#digits#function#separate#delimitator#characters#format