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: 526
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: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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: 526
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

All times are GMT -7. The time now is 02:00 PM.


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