Solved

extract number sequence in excel

Posted on 2015-01-13
3
125 Views
Last Modified: 2015-01-13
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
Comment
Question by:PeterBaileyUk
3 Comments
 
LVL 24

Accepted Solution

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

Expert Comment

by:Shaun Kline
ID: 40546566
You can use:
=SEARCH(CHAR(127),SUBSTITUTE(A2," ",CHAR(127),<n>))

Open in new window

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)))

Open in new window


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

Author Closing Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now