-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
character spacing
- Thread starter thenos09
- Start date
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 65,305
- Office Version
- 365
- Platform
- Windows
Re: Somebody please help???
If you wanted to investigate this idea,
- Are your numbers likely to be in a certain range? eg Column B starting from row 2
- Are we only talking about positive integers? What should happen if somebody entered any of these?
-12345
123.45
abcde
12ab
Then I don't know how to do that in any sensible way. Rather than format the cell (which means the underlying cell value remains as 12345 and the spaces just appear in the cell display), would it be acceptable to actually put those spaces in so the actual cell value became "12 34 5"? That would certainly be possible provided macros can be enabled.
If you wanted to investigate this idea,
- Are your numbers likely to be in a certain range? eg Column B starting from row 2
- Are we only talking about positive integers? What should happen if somebody entered any of these?
-12345
123.45
abcde
12ab
Upvote
0
macros can be enabled.
I will go into more detail in cell A1 I am writing a text string. eg. Hello my name is Brett
In cell A2 i have the formula =asc2hex(A1). This displays
48656C6C6F206D79206E616D65206973204272657474
I would like to then move this value to cell A3 and display
48 65 6C 6C 6F 20 6D 79 20 6E 61 6D 65 20 69 73 20 42 72 65 74 74
I want to be able to copy and paste cell A3 into separate software.
So the text will always be varying. At the moment I copy A2 and paste it into the other software and manually put in the spaces.
I am very new to forums. Please forgive me if Im not very good at it.
I will go into more detail in cell A1 I am writing a text string. eg. Hello my name is Brett
In cell A2 i have the formula =asc2hex(A1). This displays
48656C6C6F206D79206E616D65206973204272657474
I would like to then move this value to cell A3 and display
48 65 6C 6C 6F 20 6D 79 20 6E 61 6D 65 20 69 73 20 42 72 65 74 74
I want to be able to copy and paste cell A3 into separate software.
So the text will always be varying. At the moment I copy A2 and paste it into the other software and manually put in the spaces.
I am very new to forums. Please forgive me if Im not very good at it.
Upvote
0
Scott Huish
MrExcel MVP
- Joined
- Mar 17, 2004
- Messages
- 20,635
- Office Version
- 365
- Platform
- Windows
Infinite is not possible. The maximum text a cell can hold is 32,767 characters.
Since you are using a custom UDF for your formula why not modify that UDF to the output you desire.
But barring that:
Since you are using a custom UDF for your formula why not modify that UDF to the output you desire.
But barring that:
Code:
Function InsertSpace(r As String) As String
With CreateObject("vbscript.regexp")
.Pattern = "([A-F0-9]{2})"
.Global = True
InsertSpace = .Replace(r, "$1 ")
End With
End Function
Upvote
0
MrVillareal
Well-known Member
- Joined
- Jun 27, 2011
- Messages
- 504
Hi friends,
Type in cell A1 12345678. You can go to custom number formatting and type 00 00 00 00. This yield the result of 12 34 56 78.
Type in cell A1 12345678. You can go to custom number formatting and type 00 00 00 00. This yield the result of 12 34 56 78.
Upvote
0
Scott Huish
MrExcel MVP
- Joined
- Mar 17, 2004
- Messages
- 20,635
- Office Version
- 365
- Platform
- Windows
That won't acommodate strings of 200 or hexadecimal numbers.
Upvote
0
Scott Huish
MrExcel MVP
- Joined
- Mar 17, 2004
- Messages
- 20,635
- Office Version
- 365
- Platform
- Windows
Put that code in a standard module.
(Alt-F11, From the menu: Insert, then Module.)
Then in the spreadsheet, you can use it anywhere you want as a formula:
=InsertSpace(A2)
for Example.
(Alt-F11, From the menu: Insert, then Module.)
Then in the spreadsheet, you can use it anywhere you want as a formula:
=InsertSpace(A2)
for Example.
Last edited:
Upvote
0
Similar threads
- Solved
