CODEWITHSUNDEEP
excel
Dawn
Dawn

Reputation: 33

Extracting numbers from a list in a cell in Excel

Could anyone tell me how to extract a list of numbers from a field in an MS Excel Spreadsheet in to separate cells please? The numbers are delimited by commas. EG 506, 507, 508, 509

Thanks

Upvotes: 2

Views: 151

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

With data in A1, in B1 enter:

=IFERROR(--TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999)),"")

and copy across.

enter image description here

Upvotes: 2

Sergey Sklyar
Sergey Sklyar

Reputation: 1970

  1. Select the number cells, and click Data > Text to Columns.
  2. In the Step 1 of the Convert Text to Columns Wizard, check Delimited.
  3. Click Next to go to step 2 of the Wizard, and select the "Comma" symbol.
  4. Click Next and choose "General" type.
  5. Click Finish, and then the selected numbers are split into columns.

Ready!

Upvotes: 0

Related Questions