Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-24-2020, 05:38 PM
Marcia's Avatar
Marcia Marcia is offline Insert hyphens between a string Windows 7 32bit Insert hyphens between a string Office 2013
Expert
Insert hyphens between a string
 
Join Date: May 2018
Location: Philippines
Posts: 274
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Insert hyphens between a string

Hi. I have a column with numbers typed as
3000100123
I need to add zero before the first character, insert a hyphen after the first char, and insert another hyphen after the fifth, like
03-0001-00123


What I did to get the desired result was to create 5 helper columns parsing the numbers then joining them all. At first I tried creating a formula but gave up. How should I do it with a formula?
Thank you.
Reply With Quote
  #2  
Old 02-24-2020, 11:49 PM
ArviLaanemets ArviLaanemets is offline Insert hyphens between a string Windows 8 Insert hyphens between a string Office 2016
Expert
 
Join Date: May 2017
Posts: 564
ArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really nice
Default

Like
Code:
=TEXT(A1,"00-0000-00000")
Reply With Quote
  #3  
Old 02-25-2020, 01:48 AM
Marcia's Avatar
Marcia Marcia is offline Insert hyphens between a string Windows 7 32bit Insert hyphens between a string Office 2013
Expert
Insert hyphens between a string
 
Join Date: May 2018
Location: Philippines
Posts: 274
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by ArviLaanemets View Post
Like
Code:
=TEXT(A1,"00-0000-00000")
This simple? My experiments went to LEFT, RIGHT, LEN.... etc. This is the solution to my other problem of joining a data in percentage format with a text.
Thank you Arvi.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert hyphens between a string Macro to delete all words with hyphens? pjs15 Word VBA 2 02-04-2020 10:45 PM
Insert hyphens between a string search for string, wherever found, at end of line, insert 8 <cr>s sbktex Word VBA 2 09-17-2014 04:09 PM
Insert hyphens between a string Way to search for a string in text file, pull out everything until another string? omahadivision Excel Programming 12 11-23-2013 12:10 PM
Insert hyphens between a string Mask mode and hyphens cyndor Word 6 07-25-2012 06:40 AM
outlook task notes creates hyphens abt Outlook 0 05-09-2009 03:51 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 05:42 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft