character spacing

[thenos09]

I would just like to know how to format a cell to make a space every 2 characters

ie. cell A1 = 1234567890

I want to the cell to display 12 34 56 78 90

the data in cell A1 will vary but I always want a space after every 2nd character

 

[Peter_SSs]

Welcome to the MrExcel board!

If the number was 12345 which of the following do you want?
1 23 45
12 34 5

How long might your longest expected number be?

 

[thenos09]

Re: Somebody please help???

I would want 12 34 5

The longest number could be up to 200. I would prefer infinite.

Thanks for your help. much appreciated

 

[Peter_SSs]

Re: Somebody please help???

I would want 12 34 5

The longest number could be up to 200. I would prefer infinite.

Thanks for your help. much appreciated

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

 

[thenos09]

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.

 

[Scott Huish]

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:

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

 

[MrVillareal]

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.

 

[Scott Huish]

That won't acommodate strings of 200 or hexadecimal numbers.

 

[thenos09]

32,767 is more than enough for my project.

HOTPEPPER im not very good in excel. Do I copy that code you wrote and make a macro for the cell i want the string to go into?

 

[Scott Huish]

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.