CODEWITHSUNDEEP
excel-formula
xdennisma
xdennisma

Reputation: 29

How to create a separate column of numbers using 3-character separator?

I am trying to separate a column of data in Excel, with the numbers following the " - " in a second column. The data looks like this:

Cabin-Fever - 28,315
CANBRICKYYJ - 11
Capstone - 170,049
CB Brick Builds  - 41
CCX-Custom-Works - 53

Using the formula "=--MID(A1,FIND(" - ",A1)+3,LEN(A1))" I can move the numbers to a second column (thank you, Scott!), but the original column retains the numbers. I would like to remove the dash and numbers from column A, and have only the numbers in column B. The end result would look like:

A                  B
Cabin-Fever        28,315
CANBRICKYYJ        11
Capstone           170,049
CB Brick Builds    41
CCX-Custom-Works   53

Thank you!

Upvotes: 0

Views: 58

Answers (1)

Saif Ulislam
Saif Ulislam

Reputation: 56

You can use these formulas

trim is optional if there is empty spaces you want to remove.

=TRIM(LEFT(A2,FIND(" - ",A2)-1))

=TRIM(RIGHT(A2,LEN(A2)-FIND(" - ",A2)-1))

or

=LEFT(A2,FIND(" - ",A2)-1)

=RIGHT(A2,LEN(A2)-FIND(" - ",A2)-1)

Here is the example

Upvotes: 0

Related Questions