Hi,
how about
=LEFT(A1,5)&" "&RIGHT(A1,LEN(A1)-5)
HTH
Alan
This is a discussion on INSERT A SPACE INTO A TEXT VALUE within the Excel Questions forums, part of the Question Forums category; Hi, Hope someone can help me. I have a number of text values. However, I need to be able to ...
Hi,
Hope someone can help me.
I have a number of text values. However, I need to be able to add a space into these after the fifth character.
Any ideas how I can do this?
Thanks
Jon
Hi,
how about
=LEFT(A1,5)&" "&RIGHT(A1,LEN(A1)-5)
HTH
Alan
HTH
Alan
--------------------------------------------------------
Vlookup not sufficient? Follow the link for latest version of FuzzyVLookup or RuleLookup .
Alternatively to compare two worksheets try Compare Two Sheets
--------------------------------------------------------
There are 10 kinds of people - those who understand binary and those who don't
Try:
=REPLACE(A1,5,1,MID(A1,5,1)&" ")
"Fair Winds and Following Seas"
Fantastic.
Thanks a lot
Jon
i want ask related to above question
i have text example abc12ab
how to make it abc.12.ab
and like this too a8b6f4 how to make it like this a.8.b.6.f.4
TIA
For the original question:
=TRIM(REPLACE(A1,6,0," "))
Office 2007/2010
Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.
colibri:
Sheet1
A B 1 abc12ab abc.12.ab
Spreadsheet Formulas
Cell Formula B1 =REPLACE(REPLACE(A1,4,0,"."),7,0,".")
Excel tables to the web >> Excel Jeanie HTML 4
Office 2007/2010
Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.
The second one, I would use a UDF:
Sheet1Code:Function adddots(r As String) As String With CreateObject("vbscript.regexp") .Pattern = "(.)" .Global = True adddots = .Replace(r, "$1.") adddots = Left(adddots, Len(adddots) - 1) End With End Function
A B 1 a8b6f4 a.8.b.6.f.4
Spreadsheet Formulas
Cell Formula B1 =adddots(A1)
Excel tables to the web >> Excel Jeanie HTML 4
Office 2007/2010
Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.
The second one, I would use a UDF:
Sheet1Code:Function adddots(r As String) As String With CreateObject("vbscript.regexp") .Pattern = "(.)" .Global = True adddots = .Replace(r, "$1.") adddots = Left(adddots, Len(adddots) - 1) End With End Function
A B 1 a8b6f4 a.8.b.6.f.4
Spreadsheet Formulas
Cell Formula B1 =adddots(A1)
Excel tables to the web >> Excel Jeanie HTML 4
Office 2007/2010
Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.
ur answer working, thanks alot mr. HOTPEPPER
sorry i want ask again (reverse with my question before)
abc.12.ab how to make it abc12ab
a.8.b.6.f.4 how to make it like this a8b6f4
thank in advance
Like this thread? Share it with others