• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract the Number from text

vletm

Excel Ninja
dieselkhan
Your: i want extract the number only from thse long text
... aren't that 1st part =MID(A2,24,2) ... also numbers?
Now, Your sample result has also something else... test this
=MID(A134,35,FIND("-",A134,33)-35)
 

AlanSidman

Active Member
an alternative solution is to employ Power Query. Here is the Mcode

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "path_unix", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"path_unix.1", "path_unix.2", "path_unix.3"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Split Column by Character Transition", "path_unix.2", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"path_unix.2.1", "path_unix.2.2"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"path_unix.2.2"})
in
    #"Removed Other Columns"
Your file attached for review of this method.
 

Attachments

one more if you dont mind i tried but not success

A2 have arbaic as well

i need in arabic have
أمانة منطقة الباحة -بلدية محافظة بلجرشي

أمانة منطقة الباحة in one column
and بلدية محافظة بلجرشي this in another colimns is it possible
 
an alternative solution is to employ Power Query. Here is the Mcode

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "path_unix", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"path_unix.1", "path_unix.2", "path_unix.3"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Split Column by Character Transition", "path_unix.2", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"path_unix.2.1", "path_unix.2.2"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"path_unix.2.2"})
in
    #"Removed Other Columns"
Your file attached for review of this method.
Cant find Povery query in Excel 2016
 

vletm

Excel Ninja
dieselkhan
Hmm? You already know those two results ...
=LEFT(MID(A2,FIND("-",A2,20)+1,255),19) in one column
=RIGHT(MID(A2,FIND("-",A2,20)+1,255),25) in another colimns
 
most of result geeting this

Column one
أمانة منطقة عسير -أ
أمانة منطقة تبوك -


columns Another

منطقة حائل - المخططات.rar
atabase بلدية أبوعجرم.rar
 

vletm

Excel Ninja
dieselkhan
As You asked:
A2 have arbaic as well
You asked to 'solve' A2's value - which You already knew - without any other rules or so.
Whatever would You expect to get with other cells?
 
Here is the google sheet Sample



//olyservice/GIS-TANSIQ01/Storage/46-أمانة منطقة عسير -بلدية بللحمر/حدود القري المطلوب اعتمادهاالمعتمد مسمايتها بالوزارة.rar

and I'm Looking for in 1st column i just get أمانة منطقة عسير

and another column just get بلدية بللحمر



if there is no أمانة and بلدية word so it will come blank in columns
 
Last edited:

vletm

Excel Ninja
dieselkhan
If ... as above is clear for You then ... You could use it.
Others or at least I ... would need clear rules, what are You looking for.
Your 'if there is no...' looks clear rule.
 
dieselkhan
If ... as above is clear for You then ... You could use it.
Others or at least I ... would need clear rules, what are You looking for.
Your 'if there is no...' looks clear rule.

its is clear but .. could you check the google sheet which i shared here you will find the problem which im facing it


I highlighed the rows where is the probelm with formula
 
Last edited:

vletm

Excel Ninja
dieselkhan
Did You skip #10 reply? Please, reread it.
You have given two rules
a) which works with cell A2 - and - You've a formula
b) which works 'if there is no...'
You mentioned challenges with some formula
... I don't have any challenges and I don't know to which formula do You refer.
If I cannot get clear rules then .. good luck.
 
Top