[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# extract number sequence in excel

Posted on 2015-01-13
Medium Priority
151 Views
I have data in the form:

1963 V5A1T 1001 6960
1964 V5A1T 6961 61346
1965 V5A1T 61347 92876

I would like to extract the number sequence after the second and third spaces and store in a separate column.

not sure how
0
Question by:PeterBaileyUk

LVL 24

Accepted Solution

Phillip Burton earned 2000 total points
ID: 40546561
Why not highlight the data, and select Data - Text to Columns - select Delimited - and select "Space" and Finish.
0

LVL 27

Expert Comment

ID: 40546566
You can use:
``````=SEARCH(CHAR(127),SUBSTITUTE(A2," ",CHAR(127),<n>))
``````
to find the nth occurrence of a space. Using this with the MID formula:
``````MID(A2, SEARCH(CHAR(127),SUBSTITUTE(A2," ",CHAR(127),2)), SEARCH(CHAR(127),SUBSTITUTE(A2," ",CHAR(127),3)) - SEARCH(CHAR(127),SUBSTITUTE(A2," ",CHAR(127),2)))
``````

This formula will pull out the text between the 2nd and 3rd space if your text is in A2.
0

Author Closing Comment

ID: 40546604
Thank you worked a treat very quick and simple
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month18 days, 7 hours left to enroll