Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Split a text or numeric string into separate cells

  1. #1
    New Member
    Join Date
    Apr 2009
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Split a text or numeric string into separate cells

    Hello All

    I wonder if you could help with this one, I have a list of data like this (no spaces or other characters)

    JN551122B

    What I need to do is split each of the characters in to its own cell

    if JN551122B was in cell A1 I'd want to return:

    B2 C2 D2 E2 F2 G2 H2 I2 J2
    J N 5 5 1 1 2 2 B

    The nearest I have got is the right function, but it brings over the preceeding characters, and I need each character in its own cell. Hopefully I could copy/paste a formula and change the reference for each cell value, i.e. 1 would return J, 2 = N, 3 = 5, 4 = 5 etc.

    Thanks for your help!

  2. #2

    Join Date
    Oct 2006
    Posts
    2,541
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split a text or numeric string into separate cells

    B2

    =Mid($A1,Column(A1),1)

    then copy to the right

  3. #3
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    8,811
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split a text or numeric string into separate cells

    OR, use Data, Text to Columns, and specify field width as 1.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  4. #4
    New Member
    Join Date
    Dec 2013
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Split a text or numeric string into separate cells

    Quote Originally Posted by Seiya View Post
    B2

    =Mid($A1,Column(A1),1)

    then copy to the right

    what a great idea

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •