Hello,

Could someone please advise on how to format text & numbers in the same cell
as shown below:

I want the following format as shown in the examples given:

Example 1: 019 APR
Example 2: 530 OCT

The user may enter the information in a number of ways as shown in the
examples shown below:

Example 1: 019APR
Example 2: 19APR
Example 3: 019 APR
Example 4: 19 APR

Any help would be greatly appreciated,

Kind regards,

Chris.

I'd use a helper cell and another cell to display the result.

Say the value is in A1. Then in B1, I'd put this formula:
=LEFT(MATCH(FALSE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Then in the result cell (C1 for me), I'd use this formula:
=TEXT(--LEFT(A1,B1-1),"000")&" "&MID(A1,B1,255)



Chris Hankin wrote:

Hello,

Could someone please advise on how to format text & numbers in the same cell
as shown below:

I want the following format as shown in the examples given:

Example 1: 019 APR
Example 2: 530 OCT

The user may enter the information in a number of ways as shown in the
examples shown below:

Example 1: 019APR
Example 2: 19APR
Example 3: 019 APR
Example 4: 19 APR

Any help would be greatly appreciated,

Kind regards,

Chris.

--

Dave Peterson