How to transfer data from one cell to columns when there is no delimiter?

[jasmin_oe]

Hey there,

I got a major problem with my dataset (500+ rows) and since I am not an Excel Pro and couldn't find answers on the internet I am posting my first ever question online:

Okay here is the issue, I want to put every number of this column in another cell, like I did it for the first two rows:

View image: excel problem

Normally it would easily work with the Text to Column function but since I have no delimiter I am completely swamped.
Is there a way to solve this problem with a function?

Otherwise, if this is impossible, maybe there is a quick way to insert commas or spaces between each number so that I could use the Text to Column function?

I would appreciate any feedback!
Thanks for your time!

Best,
Jasmin

 

[mumps]

Put this formula in cell B1: =MID($A1,1,1) Copy it across to column O and then increase the "1" in the middle by 1 in each column. For example: in C1: =MID($A1,2,1), in D1: =MID($A1,3,1), etc. Then highlight all the cells from B1 to O1 and drag the formulas down to your last used row. It's a little tedious but it works. There may be a better way.

 

[XLearner]

Hi

Assuming the big numbers are in column 1, starting row 1. In B1 enter:

=--MID($A1;COLUMN()-1,1)

Then copy right and down as necessary.The -- at the beginning coerce the extracted character to its numerical value

Regards
XLearner

 

[JLGWhiz]

Hi *jasmin_oe, welcome to the Forum.
As an alternative to formulas, you can use a macro.
You can copy this to your standard code module. Your workbook will need to be saved as a macro enabled workbook (.xlsm) with security settings to access VBA and allow macros to run. Once your system has beer configured to use macros, you can access the code module by pressing Alt + F11. If the large pane is dark when the VB Editor opens, then click Insert on the editor menu bar, then click Module. The screen should brighten and you can then copy the code into the large pane. If the screen is already bright, then double click Module1 in the VBAProject pane to ensure you copy to the correct code window.

Sub t()
Dim c As Range, i As Long
With ActiveSheet
    For Each c In Range("A1", .Cells(Rows.Count, 1).End(xlUp))
        For i = 1 To Len(c.Value)
            .Cells(c.Row, i + 1) = Mid(c.Value, i, 1)
        Next
    Next
End With
End Sub

To run the code from the Excel window, press Alt + F8, then double click the macro name.

 

[jasmin_oe]

wow, thank you so much for your quick answers, they all worked absolutely fine!
you saved my day!

all the best!

 

[JLGWhiz]

wow, thank you so much for your quick answers, they all worked absolutely fine!
you saved my day!

all the best!

We aim to please,
Regards, JLG