Share this content
0
8
845

Splitting a cell after 5 characters

I have a UTR in cell A1 in the format 1234512345.  My OCD makes me need to make that 12345 12345 (I'm updating the database to filling gaps and the existing entries are in this format).

I can see how to split to cells based on there being a tab/comma/space (then I would concatenate), but I can't see how to do it by character.

So how would I make 1234512345 into 12345 12345?

Replies

Please login or register to join the discussion.

22nd Feb 2013 15:15

Use string handling

String handling (left & right) and concatenation will do the job.

PS. Sorry. Meeting due so haven't time to work out the formula.

Thanks (0)
22nd Feb 2013 15:16

Thanks

ShirleyM wrote:

String handling (left & right) and concatenation will do the job.

PS. Sorry. Meeting due so haven't time to work out the formula.

Cunning!  I wasn't aware of Left and Right as functions, thanks.

Oddly a search in Google for 'String Hand' suggests 'String Handcuffs' as the best suggestion.  Worrying...

Thanks (0)

Er...

... =IF(LEN(TRIM(A1))=10,LEFT(TRIM(A1),5)&" "&RIGHT(TRIM(A1),5),"ERROR")

Thanks (0)
22nd Feb 2013 15:20

Concatenate works

=CONCATENATE(LEFT(A1,5)," ",RIGHT(A1,5))

Assuming UTR in cell A1.

Thanks (0)
avatar
By ACDWebb
22nd Feb 2013 15:27

Why not use a Custom number format of

00000 00000

Thanks (0)
22nd Feb 2013 15:29

Why the hell...

ACDWebb wrote:

00000 00000

Why the hell didn't I just think of that!  I blame Friday...

And I must admit I do like George's version, but Left/Right/Concatenate did it fine.

Thanks (0)
avatar
By Old Greying Accountant
22nd Feb 2013 15:32

Hmmm ...

ACDWebb wrote:

00000 00000

... works for me, but have learnt a new word, even if it does make me think of what a dyslexic has to do to read a book!

Thanks (0)

Or...

... =REPLACE(A1,6,0," ")

Thanks (0)