Extract the Number from text

[dieselkhan]

Please anyone can help me i want extract the number only from thse long text

 

[vletm]

dieselkhan
for cell B2 =MID(A2,24,2)& SUBSTITUTE(MID(A2,35,3),"-","")
and copy down

 

[dieselkhan]

getting this as well i just used this
i just need tis number

=SUBSTITUTE(MID(A134,35,3),"-","")

 

[vletm]

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]

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

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.

 

[dieselkhan]

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

 

[dieselkhan]

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

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]

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

 

[dieselkhan]

most of result geeting this

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


columns Another

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

 

[vletm]

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?

 

[dieselkhan]

Here is the google sheet Sample

Data Upload Statiscs

data upload path_unix,path_windows,path_rootname,path_filename,path_extension,path_filesize,path_ownername,path_readonly,path_modified_date,path_created_date,path_accessed_date,path_directory_unix,path_directory_windows,path_type,USERID,Amana //olyservice/GIS-TANSIQ01/Storage/10-أمانة منطقة البا...

docs.google.com

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

 

[vletm]

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]

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

 

[vletm]

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.

 

[AlanSidman]

Cant find Povery query in Excel 2016

In 2016, it is called Get and Transform and is located on the Data Tab. Also, there is a link in my signature that you should not ignore.

Google is your friend.