Format numbers and symbols with trailing spaces

[Martin_H]

Hi team,

random cell in Excel contains this text: 97A346V21ANQ (as you can see there are numbers and letters). I would like to see it like this: 97A 346 V21 ANQ (mind the gaps).

Would it be possible to create a custom format?

Thank you.

 

[Marc L]

Hi,​

​

yes just combining the character @ and the space …​

 

[Martin_H]

Hi,​

​

yes just combining the character @ and the space …​

Hi Marc,

could you give me an example please?

 

[Marc L]

​

@@@ @@@ …​

 

[Martin_H]

Well that copies the entire sentence according to the number of @ symbols used.

 

[Marc L]

​

Your initial post was not enough explicit : so now for a partial text within a sentence I can't see any cell format solution …​

 

[Jon von der Heyden]

Your initial post was not enough explicit : so now for a partial text within a sentence I can't see any cell format solution …

Nonsense! You just didn't pay attention to the question, posted something without testing it, and now you're trying to shift the blame to the OP. If you're going to respond to posts on the forum then please do so with due care. Many people skip over threads with replies and so you're doing the OP a disservice by not making a proper effort to help.

@Martin_H - as far as I know it can't be done with custom number format. If I can point you toward a formula suggestion, this may work if you are splitting segments of three characters of a 12 character string.

Using REPLACE():

=REPLACE(REPLACE(REPLACE(A1,4,0," "),8,0," "),12,0," ")

With TEXTJOIN function (I don't think you have this):

=TEXTJOIN(" ",TRUE,MID(A1,1*{1;4;7;10},3))

 

[Martin_H]

Nonsense! You just didn't pay attention to the question, posted something without testing it, and now you're trying to shift the blame to the OP. If you're going to respond to posts on the forum then please do so with due care. Many people skip over threads with replies and so you're doing the OP a disservice by not making a proper effort to help.

@Martin_H - as far as I know it can't be done with custom number format. If I can point you toward a formula suggestion, this may work if you are splitting segments of three characters of a 12 character string.

Using REPLACE():

=REPLACE(REPLACE(REPLACE(A1,4,0," "),8,0," "),12,0," ")

With TEXTJOIN function (I don't think you have this):

=TEXTJOIN(" ",TRUE,MID(A1,1*{1;4;7;10},3))

Perfect! Thank you very much.

 

[Marc L]

​

Sorry as I could not test as this time and I confused with the VBA Format function where the character @ well works for the formatting​

so it can be also easily achieved via an UDF or a classic VBA procedure …​