|
|
|
|
#1
11-28-2024, 07:03 AM
|
|||
|
|||
Extracting several portion of text/numbers from one string
Hallo
 I would need to extract, from one string, several portions, as in the example in the attached file. The first string (A1) is the header, then A2, A3 are the data to align below the header. Could you help me on this? Thanks!
|
|
#3
11-28-2024, 08:19 AM
|
|||
|
|||
|
Quote:
|
|
#4
11-28-2024, 12:04 PM
|
||||
|
||||
|
In your attached file:
Select the data (A1:A5) In the Data tab of the ribbon, choose Text to Columns in the Data Tools section and do the following: 2024-11-28_184526.jpg click Next In the next step ensure Treat consecutive delimiters as one is empty and the delimiter is set to only a comma, click Next 2024-11-28_184621.jpg In the 3rd step select the first column at the bottom and choose the option button for that column to be Do not import column (skip), leave the rest of the columns as they are. In the Destination field choose a cell where the information is going to go and click Finish. 2024-11-28_184845.jpg That's it. However, it looks as though the data comes pasted from a text file of some sort, and if you're doing this on a regular basis it would be easier to import directly from the file using Power Query which is built-in to your version of Excel.
|
|
#5
11-28-2024, 01:28 PM
|
|||
|
|||
|
That's great! thanks!
I also tryed Power Query but it's not easy, I can't do. So I will follow the first way you showed. Thank you again!
|
|
#6
11-29-2024, 01:08 AM
|
||||
|
||||
|
Power Query solution, easier to update , you don't have to go through all the steps again
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17", "Column1.18", "Column1.19", "Column1.20", "Column1.21", "Column1.22", "Column1.23", "Column1.24", "Column1.25", "Column1.26", "Column1.27", "Column1.28"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1.1"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true])
in
#"Promoted Headers"
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
|
|
#7
11-29-2024, 04:11 AM
|
|||
|
|||
|
Thanks Pecoflyer, I am curious to use Power Query, I tried again but I was not able to do. I will use the simpler method.... Thanks so much!
|
|
#8
11-30-2024, 09:30 AM
|
||||
|
||||
|
You're welcome Where did it go wrong?
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
|
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Extracting Numbers from String | KBeMe | Word VBA | 1 | 09-25-2019 08:51 PM |
| Help to extracting text from text string | Haha88 | Excel | 3 | 02-13-2019 05:47 AM |
| Help with Formula to extracting text from text string | Haha88 | Excel | 7 | 01-13-2019 01:33 AM |
 Help with Formula to extracting text from text string
|
Haha88 | Excel | 9 | 02-05-2018 01:04 AM |
|
Extracting a phone number from a string that contains text and numbers.
|
hommi16 | Excel | 2 | 06-05-2013 09:19 PM |
Windows XP 
Office 2010 32bit
Hybrid Mode