# Extracting Inconsistent Data

1. ## Extracting Inconsistent Data

Attachment 541811

Hello,

I'd like to extract the BNR # from column A and show it as I mocked up in B. I have a sheet with 1000s of records without a consistent format. Any help would be appreciated.

Thanks!
Megan

2. ## Re: Extracting Inconsistent Data

Hi Megan, welcome to the forum!

I would use a helper column here (2 columns with formulas).

B1 =TRIM(RIGHT(SUBSTITUTE(A1,"BNR",REPT(" ",LEN(A1))),LEN(A1)))
C1 =IFERROR(LEFT(B1,FIND("/",B1)-1),B1)

Drag down as far as needed.

3. ## Re: Extracting Inconsistent Data

ARRAY formula in B2, then drag down

=IFERROR(LARGE(IF(ISNUMBER(LEFT(RIGHT(A1,LEN(A1)-FIND("/BNR ",A1)-5),ROW(INDIRECT("1:"&LEN(A1)-FIND("/BNR ",A1)-5)))+0),LEFT(RIGHT(A1,LEN(A1)-FIND("/BNR ",A1)-5),ROW(INDIRECT("1:"&LEN(A1)-FIND("/BNR ",A1)-5)))+0,""),1),"")

4. ## Re: Extracting Inconsistent Data

Thank you this worked!

5. ## Re: Extracting Inconsistent Data

Thank you for coming back to let us know. Which solution worked for you?

6. ## Re: Extracting Inconsistent Data

Here is another one
Enter in B1 and copy down
Formula:
`Please Login or Register  to view this content.`

 v A B 1 This text is just a test/text /just a test /BNR 321654 321654 2 This text is just a test/text /BNR 987654323.25/Additional requirements 987654323.3 3 This text is just a test/text /BNR 156.35/CSST Testing 156.35 4 This text is just a test/text / additional text/BNR 89456123.2455/Status/Open 89456123.25

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1