+ Reply to Thread
Results 1 to 3 of 3

separating a single text field with different values into new fields for analysis

  1. #1
    Registered User
    Join Date
    01-27-2012
    Location
    Berkeley, California
    MS-Off Ver
    Excel 2016
    Posts
    33

    separating a single text field with different values into new fields for analysis

    I imported some streamflow data from USGS that imported as several fields merged into a single text column. I now need to go through the text column and extract the date and flow data in separate columns for analysis.

    i.e. I have several hundred lines of data in a single column that looks like this "USGS 11181390 1976-10-12 0.09 A"

    Is there a way to extract the date (i.e. 1976-10-12) [through 1996-06-09] and the numerical value after the date, in this case = "0.09" and put them in different columns so I can analyze flows by date. The location of the beginning of each field seems to be the same, the ending changes for the numerical values for the flow data varies a little since some of the flow data is only to tenths, i.e. - 0.1 and not 0.10.

    Thanks, R

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: separating a single text field with different values into new fields for analysis

    you can use the Text to Column functino in excel to accomplish this, just select the columns and go to the ribbon, select Data ->Text to Column ->Delimited -> check Space ->Finish

    from here your data shoudl be much easier to work with
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: separating a single text field with different values into new fields for analysis

    For the date: =MID(A1, FIND("-", A1)-4, 10)
    For the data: =TRIM(MID(A1, FIND(".", A1)-2,5))+0
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1