INSERT A SPACE INTO A TEXT VALUE

JonRowland

Active Member
Joined
May 9, 2003
Messages
415
Office Version
  1. 365
Platform
  1. Windows
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
 
I need to have my date field surrounded by spaces.

End result would be:

[space]111018[space]
It would be a better idea to start a new thread rather than jump onto a nearly 6-month old thread. When you do, please provide a little more information, such as... is your date always presented without delimiters? is your date always 6-digits long (2 digit month, day and year)? is there other numbers in the text besides your "date"? anything else about your data that we should know?
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Edit: Sorry, didn't see that you were going to start a new thread. :)

Like this?

=" "&A1&" "
 
Upvote 0
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

Quick way is to choose the Find / Replace option on ribbon. In the Find box put . and leave the replace box empty.
 
Upvote 0
Quick way is to choose the Find / Replace option on ribbon. In the Find box put . and leave the replace box empty.
I'm not sure if you realised that the question you answered was from over four months ago, or that the answer you gave was given way back then too. ;)
Select the column and/or row of (or simply the) cells with your text, click Edit/Replace and put the dot in the "Find what" field, leave the "Replace with" field empty, then click the "Options>>" button (assuming the options are not already displayed) and make sure the "Match entire cell contents" checkbox is not checked, then click the OK button.
 
Upvote 0
s=replace(s,".","")
I feel like I'm getting a bit repetitive here but ...

I'm not sure if you realised that the question you answered was from over four months ago, or that the answer you gave was given way back then too. ;)

.. this should work for you...
Code:
Function RemoveDots(S As String) As String
  RemoveDots = Replace(S, ".", "")
End Function
 
Upvote 0
I am repeating

Thanks Peter

I do appreciate the terific job that you do in fixing the errors posted by me and my kind

Harry S
 
Upvote 0
Have not read all of your 20,000 posts yet but have learnt much from those that I have read


Looking forward to your 30,000 th post
Harry S
 
Upvote 0
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

Same type of question, however, I need to add . back into a column of email address that were incorrectly changed to have the . removed

Thank you in advance!
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top