CODEWITHSUNDEEP
stringexcel-formula
Rudra
Rudra

Reputation: 159

retrieving values by specifying index postion

I have a column which looks something like below:

1235hytfgf ui
3434jhjhjh ui
6672jhjkhj ty

I have to name 1st four characters as numbers; the next 6 characters as type and last 2 as id; which function should I use to say that from index(0-3) should be numbers and index(4-9) : type

Upvotes: 0

Views: 28

Answers (2)

Forward Ed
Forward Ed

Reputation: 9874

LEFT(), RIGHT() and MID() are three functions used to rip out portions of a string.

If you data was starting in B2 you could use the following formula in an empty cell to pull the first 4 charters.

LEFT(B2,4)

Now that will pull the first 4 characters and leave them as characters. If you want the numbers as a string to be converted to a numeric value then one of the easy ways to convert it is to send it through a math operation which does not change its value. *1, +0, -0, /1 and -- all work. You formula may look like one of the following:

--LEFT(B2,4)
LEFT(B2,4)+0
LEFT(B2,4)*1
LEFT(B2,4)/1
LEFT(B2,4)-0

To grab the middle portion of the string, use the MID function. Since you already know the starting position and length of the string to pull you can hard code the information into your formula and it will look as follows:

MID(B2,5,6)

5 is the starting position for which character to start pulling from, and 6 is the length of the string to pull or number of characters to pull.

To get the last 2 characters, similar to the first function, use RIGHT(). The formula would look as follows:

RIGHT(B2,2)

Upvotes: 2

Terry W
Terry W

Reputation: 3257

If you are dealing with a large amount of data, say over thousands of records, try power query in excel.

Please refer to this article to find out how to use Power Query on your version of Excel. It is available in Excel 2010 Professional Plus and later versions. My demonstration is using Excel 2016.

Steps are:

  1. Add your one-column data to Power Query Editor;
  2. Highlight the column, use Split Columns function and select By Digit to Non-Digit, it will separate the first nth numerical characters from the string;
  3. Highlight the second column, use Split Columns function again and select By Delimiter and use space as the delimiter, it will separate the type and id as desired;
  4. Renamed the columns, and you should have something like the following:

Demo

You can then Close & Load the output to a new worksheet (by default).

Here is the power query M Code behind the scene for reference only. All functions used are available in GUI so should be easy to execute.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Split Column by Character Transition", "Column1.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}, {"Column1.2.1", type text}, {"Column1.2.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1.1", "numbers"}, {"Column1.2.1", "type"}, {"Column1.2.2", "id"}})
in
    #"Renamed Columns"

Let me know if you have any questions. Cheers :)

Upvotes: 0

Related Questions