Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default Formula to extract certain texts

Good day.I need help to extract certain texts in column A to place to columns B,C and D as in the attached file. Those are the ID, azimuth and distance generated by CAD lisp. I did the first three manually but i see a formula in columns B,C and D does best. Thank you ExcelBanter
Attached Images
 
Attached Files
File Type: zip get#AziDist.zip (11.2 KB, 26 views)
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Formula to extract certain texts

Hi,

Am Tue, 7 Feb 2017 15:19:20 +0000 schrieb ecdlc888:

Good day.I need help to extract certain texts in column A to place to
columns B,C and D as in the attached file. Those are the ID, azimuth and
distance generated by CAD lisp. I did the first three manually but i see
a formula in columns B,C and D does best. Thank you ExcelBanter


try in B1:
=IF(LEFT(A1,5)="Sshot",--MID(A1,FIND("<",A1)+1,FIND("",A1)-FIND("<",A1)-1),"")
in C1:
=IF(LEFT(A2,5)="Sshot",MID(A2,FIND("=",A2)+2,FIND( """",A2)-FIND("=",A2)-1),"")
and in D1:
=IF(LEFT(A2,5)="Sshot",--MID(A2,FIND("Distance",A2)+12,FIND("#",SUBSTITUTE( A2,".","#",2))-FIND("Distance",A2)-12),"")

and copy the formulas down.


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Formula to extract certain texts

Hi,

Am Tue, 7 Feb 2017 17:30:45 +0100 schrieb Claus Busch:

try in B1:
=IF(LEFT(A1,5)="Sshot",--MID(A1,FIND("<",A1)+1,FIND("",A1)-FIND("<",A1)-1),"")
in C1:
=IF(LEFT(A2,5)="Sshot",MID(A2,FIND("=",A2)+2,FIND( """",A2)-FIND("=",A2)-1),"")
and in D1:
=IF(LEFT(A2,5)="Sshot",--MID(A2,FIND("Distance",A2)+12,FIND("#",SUBSTITUTE( A2,".","#",2))-FIND("Distance",A2)-8),"")


have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgYo_mMuWeih71CaoXQ


Regards
Claus B.
--
Windows10
Office 2016
  #4   Report Post  
Junior Member
 
Posts: 3
Default

Quote:
Originally Posted by Claus Busch View Post
Hi,

Am Tue, 7 Feb 2017 17:30:45 +0100 schrieb Claus Busch:

try in B1:
=IF(LEFT(A1,5)="Sshot",--MID(A1,FIND("<",A1)+1,FIND("",A1)-FIND("<",A1)-1),"")
in C1:
=IF(LEFT(A2,5)="Sshot",MID(A2,FIND("=",A2)+2,FIND( """",A2)-FIND("=",A2)-1),"")
and in D1:
=IF(LEFT(A2,5)="Sshot",--MID(A2,FIND("Distance",A2)+12,FIND("#",SUBSTITUTE( A2,".","#",2))-FIND("Distance",A2)-8),"")


have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgYo_mMuWeih71CaoXQ


Regards
Claus B.
--
Windows10
Office 2016
Hello Claus,
That works very very good.
Thank you and regards,
ecdlc

But wait, when i copy-drag the formula at distance column, the distances decimal point disappear

Last edited by ecdlc888 : February 8th 17 at 06:15 AM Reason: add remark
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
extract formula result form cell without running formula again jason Excel Programming 4 August 14th 09 02:01 AM
extract formula result form cell without running formula again jason Excel Programming 0 August 6th 09 07:40 PM
Counting texts computexcel Excel Discussion (Misc queries) 2 August 24th 08 12:56 AM
Extract texts & numbers from one cell into four cells Ed Excel Discussion (Misc queries) 3 January 25th 08 05:57 AM
extract a foldername with a formula-update formula does not work solo_razor[_16_] Excel Programming 2 October 30th 03 04:59 PM


All times are GMT +1. The time now is 12:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"