Extract a number from the data string in Cell

[abdulkharij]

Hi Dears,

I would like to ask if it's possible to extract a number 500005 to Cell C2 from the following data string in Cell A2 :

[TABLE="width: 423"]
<tbody>[TR]
[TD="class: xl63, width: 423"]Cost Center: 4545544 Provo

Earning: Miscellaneous (MISC)

Employee: XXXXXXX

Fund: 113 Sponsored Programs

Job Profile: Assistant - BIOL.

Line of Business: LOB002 Traditional

Location: Main Campus

Pay Group: Semi-Monthly Exempt

Position: Assistant, Biology

Program: 200 Sponsored Programs

Run Category: Regular

Simmons Grant: 454545 Davis Educ Fdtn-PLAN Prof Dev

Spend Category: Salaries - Faculty Extra Comp (500005)[/TD]
[/TR]
</tbody>[/TABLE]


Thank you,
ABDEL

 

[Rick Rothstein]

One example in not much to go one, but assuming it is representative and that the number you want is always located at the end of the text and is always encased in parentheses...

=-TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

 

[Joe4]

Assuming that the number you want is always at the end and is in parentheses, you can use this formula (for an entry in cell A1):

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"(",REPT(" ",255)),255)),")","")

 

[abdulkharij]

Thank you so much for your help. How about if I want to get the following :
Salaries - Faculty Extra Comp (500005)

One example in not much to go one, but assuming it is representative and that the number you want is always located at the end of the text and is always encased in parentheses...

=-TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

 

[Haluk]

Using Joe4's formula;

=TRIM(RIGHT(SUBSTITUTE(A1;":";REPT(" ";255));255))

Depending on your Excel settings, change semicolon characters (;) with comma (,)

i.e.;

=TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",255)),255))

 

[abdulkharij]

Thank you so much for your help. How about if I want to get the following :
Salaries - Faculty Extra Comp (500005)

 

[Rick Rothstein]

Thank you so much for your help. How about if I want to get the following :
Salaries - Faculty Extra Comp (500005)

Maybe this...

=MID(A1,SEARCH("Spend Category: ",A1)+16,300)

 

[abdulkharij]

Perfect Thank you so much for your great help

 

[admiral100]

Another option =RIGHT(A1,LEN(A1)-FIND("Salaries",A1)+1)

 

[Rick Rothstein]

Another option =RIGHT(A1,LEN(A1)-FIND("Salaries",A1)+1)

I would suspect that an item named "Spend Category" could start with words other than "Salaries".