VLOOKUP formula to extract $ and , special characters

[sneakyla]

Hello!

I'm currently using VLOOKUP twice to extract data from 2 different cells by adding & but doing this isn't extracting any $ or , characters.

One cell contains $price and the other cell contains a large number than should include a , if the number goes over 1,000.

For example.

A1 (cell with formula to extract data from B1 and C1)
5.00 | 4000

B1
$5.00

C1
4,000

Would anyone know how to fix this?

Thank you!

 

[Oaktree]

Use the TEXT function.

i.e. instead of A1=B1&"|"&C1 use A1 =TEXT(B1,"$#,##0.00")&"|"&TEXT(C1,"#,##0")

 

[sneakyla]

Use the TEXT function.

i.e. instead of A1=B1&"|"&C1 use A1 =TEXT(B1,"$#,##0.00")&"|"&TEXT(C1,"#,##0")

Hello!

Thank you for the response!

I totally forgot to include that the reason why I used VLOOKUP is because I need it to look up a specific value and extract data from that row.

A column
Apple
Orange

B Column
$5.00
$10.00

C Column
4,000
5,000

D1
apple
5.00 | 4000 (formula finding value and returning data from apple (d1)

D2
orange
10.00 | 5,000 (formula finding value and returning data from orange (d2)

Thank you!

 

[Oaktree]

Same idea, just substitute your VLOOKUP formulas for B1 and C1 in my previous post

 

[sneakyla]

Same idea, just substitute your VLOOKUP formulas for B1 and C1 in my previous post

Works perfectly.

Thank you so much!